One of our clients' SQL Servers got corrupted. we were forced to uninstall all instances and all SQL Server programs in add/remove programs and then reinstall SQL Server because their backups were failing and useless.
I was able to re-attach the user databases but now I need to recover the SQL Server agent jobs, so first I tried creating a user database to hold the old msdb data called
MSDBData_OLD, stopping the SQL Server service, replacing the
.mdf file with the corrupted msdb's
.mdf file and deleteing the
.ldf file, which would normally result in the database getting marked SUSPECT, and I could take it from there, but when I start the service again and click the database it says:
The database MSDBData_OLD is not accessible (ObjectExplorer)
Next I tried attaching with the log file and I got this:
Finally, I tried simply attaching the msdb database as a user database, but when I do, I get:
File activation failure. The physical file name "D:\SQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf" may be incorrect. The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
D:\SQL\MSSQL10_50_.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf is the path of the old and restored SQL Server
msdb database log file.
We are running SQL Server 2008 R2 64 bit SP1.
Is there any way to recover from this or am I forced to rebuild the jobs from scratch?