A story of corrupted Master database in SQL Server 2008

  • It used to be you would restore everything from the same backup set in case there was activity in the user databases that would put them out of synch with the meta data in the master. But in SQL Server 2008 that doesn't seem to be the case as system object meta-data isn't stored in master.

    I know if there are users not defined in master that are given permissions in user databases, that can cause issues but in my experience with detaching/attaching databases in 2008, it can be handled from SSMS.

    So one does wonder why not just attach the user DBs.

  • Good article yogesh ! 🙂

    ____________________________________________________________________
    Going in one more round when you don't think you can - that's what makes all the difference in your life --Rocky Balboa

    Believe you can and you're halfway there. --Theodore Roosevelt

  • I do appreciate the article. I really do hope that the root cause of the corruption is explained.

    I must say that in 5-ish years of 2005, 10-ish years of 2000, I don't think we've ever had a master database corruption - in and of itself. We're not processing millions of rows per hour, either, which I know some of you are, and perhaps that is a factor.

    I agree with the idea that Monday naps could be spent in better ways, perhaps.

    😉

  • How did u find that master database was corrupted from eventviewer.

    didn't u tried to attach user database instead for restore.

  • I recently went through a corruption issue with the master and/or msdb on a SQL 2000 server. It was a test server fortunately. I battled for over a day with trying to recover the system databases with command line startup options, single user mode, sp_detach_db and sp_attach_db - kept failing with the tempdb even though I dropped it and recreated it. The fix for me was rebuildm.exe after copying template system DB's from a SQL 2000 CD. Once I had the database services started I restored the master, msdb and model.

    But all the user databases were fine - I did detach and reattach all the user databases and manually ran checkdb. I'm not sure why you'd have to restore from previous backups without at least trying checkdb on the existing databases. Going backwards in time = data loss in most transactional systems and at least in my book is a last resort, especially on user DB's - true?

    btw - my system db injuries were self-inflicted. I was in the process of moving all the database files (.mdf, .ndf, .ldf files) to dedicated file systems. I've done this several times but with this particular system I got out of step somewhere along the process and got the system DB confused about tempdb. There may have been a more elegant way to recover but I didn't find it. Moving master, model, msdb and tempdb is pretty straight forward on SQL 2000 but it's not as easy as on 2005/8. <-- my experience

  • RyderD (5/17/2011)


    I do appreciate the article. I really do hope that the root cause of the corruption is explained.

    I must say that in 5-ish years of 2005, 10-ish years of 2000, I don't think we've ever had a master database corruption - in and of itself. We're not processing millions of rows per hour, either, which I know some of you are, and perhaps that is a factor.

    I agree with the idea that Monday naps could be spent in better ways, perhaps.

    😉

    never seen one either but it's possible. HP has constant RAID hardware and hard drive firmware and driver updates. i've had servers reboot and shut down without a proper shutdown. earlier this month i replaced 2 power supplies in an older HP server. the server was shutting down every 3 days almost on the dot. apparently HP knew that some of it's power supplies had issues because during the call they asked me for the part numbers of the ones i had.

    it's possible to have I/O corruption without messing around in the master db

Viewing 6 posts - 31 through 35 (of 35 total)

You must be logged in to reply to this topic. Login to reply