Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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:

SQL Error

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?

share|improve this question
If you installed the instance to the same directory as the previous install you can just replace the MDF/LDF files for msdb and the jobs should be there when you bring the instance back up. You should not need to remove the LDF file at all. – Jason Cumberland Nov 9 '12 at 19:17
sorry, this didn't work for me. after replacing the files i was unable to start the sql server agent service and the msdb database was marked as suspect. repair attempts on the database using DBCC failed... – RoastBeast Nov 14 '12 at 20:12

migrated from stackoverflow.com Nov 9 '12 at 18:18

2 Answers

up vote 1 down vote

I think there is no option but to rebuild system databases. This article from Microsoft explains how to do it.

share|improve this answer
this is what i ended up doing and i have to recreate all of the jobs from scratch...ugh...lesson learned on verifying backups on a regular basis! – RoastBeast Nov 14 '12 at 20:19
up vote 0 down vote

Ensure that the SQL Server Service account can read and write the files you are attaching. When moving these files between severs, it is easy to get the ACLs wrong. I think that you should be able to attach the MDF and ldf using a different database name in order to try to pull out some of the data to reconstruct the old jobs. That reconstruction job may be tedious.

share|improve this answer
no, i cannot attach these files, even when using a different database name. when i attempt this, i get the "could not redo log record..." error that is depicted in the image above. also, i am not moving files between servers, i am trying to restore to the same server. – RoastBeast Nov 14 '12 at 18:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.