In the first post of the year I’d like to kick off a survey. I’m interested to know whether you’ve ever practiced restoring or rebuilding master, or whether you’ve had to do either for real in a disaster recovery situation.
I’ll editorialize the results next week.
9 thoughts on “Survey: restoring and rebuilding master”
I have practiced restoring master databases in the past to make sure I could answer the questions properly in cert exams I never took.
The one time I actually had a master database become corrupted I didn’t bother with restoring. I rebuilt the entire SQL install (SQL2005 if I remember correctly).
I have practiced the rebuilding and restoring of master. I have had to restore master in an emergency situation and was successful.
Had to do it in a DR situation once. Hadn’t practiced before hand!
Once I was asked to restore the master from production to a blank system. We wanted to see if a software upgrade had deleted a user account. Something that I thought would take me five minutes, took way (way, way, way) to long. I messed up because the versions of SQL Server not matching.
I had to rebuild the master on a production system before as the nightly backups were insufficient to restore from tape. Not tricky, but took longer than expected.
Yes. I’ve practiced it and it was an eye opener. I learned how to use SQLCMD;) I went so far as to corrupt master so that SQL Server wouldn’t come up and you had to run setup.exe and rebuild master then restore it. I’m glad I went through that exercise. It might be time to do that again now that you bring it up.
Yes, thanks for this reminder to practice again. I too had to do this on production and again as practice in a test environment. In both cases, it was not as easy as I had thought and it took much longer than I would have guessed.
I had to rebuild the master db every time when I have to participate in periodical tests of backups.
JD: I also learned case sensitivity in the -m”SQLCMD” flag. (I believe it must be all caps.) That’s one of the reasons it’s good to drill in live circumstances if you can. There’s lots of little stuff you’ll never think about until you’re in crisis mode.
(For anyone who doesn’t know, -m”SQLCMD” means that SQL will use only one connection, and it must say that it’s running SQLCMD. It’s no good for security, because an app can present anything to SQL as its name. But if you have a web server trying to connect to SQL, it is a quick and dirty way to block the web server and have it accept SQLCMD. That is: if you realize that it’s case sensitive, otherwise you can spend a long time wondering why the heck you can’t log in.)