question

ruancra avatar image
ruancra asked

Retrieve data from corrupt MSDB

Hi All The MSDB database on our development server was marked as Suspect, there was no backup. I copied the .mdf and .ldf to a different location and ran the instmsdb.sql script to rebuild the MSDB from scratch. All scheduled jobs, job history among other info are now lost. I need a list of the last restore date of all databases on that instance. When trying to attach the .mdf and .ldf, i get this error: "The log scan number (363321:176:24) passed to log scan in database 'MSDB2' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup." When trying to attach the .mdf only, i get this error:"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." Using SQL 2008 Any ideas?
sql-server-2008corruptionmsdb
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Remember, if an answer to your question is helpful, vote it up by clicking on the thumbs up symbol next to it. If the answer solves your problem, be sure to click the check box next it to identify it as the solution. Do this for all your questions.
0 Likes 0 ·

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
The only option you have at this point would be to pay someone [like Paul Randal][1] who knows how to modify the internal structures of a SQL Server database to attempt a recovery, and there's no guarantee. Your database is corrupted. It's gone. Set up backups on the new server. And test the backups to ensure they're working. [1]: http://www.sqlskills.com/
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I was just reading that one too. One option @runcra could try is to do a DBCC with REPAIR. That might save your data. You could also try REPAIR_ALLOW_DATA_LOSS, but, as the name implies, everything could go away.
1 Like 1 ·
Ooops. Forgot about that. No. You're essentially stuck with no options. Sorry.
1 Like 1 ·
Heh. Nice timing - Paul blogs about corruption on [SSC][1], and this question comes along... [1]: http://www.sqlservercentral.com/articles/Corruption/96117/
0 Likes 0 ·
Thanks for the feedback, is it possible to apply a DBCC with REPAIR or REPAIR_ALLOW_DATA_LOSS with a .mdf file? As this is all i have...
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.