It’s been a hectic January with client work and preparations for our 2014 classes so I haven’t blogged as much as I wanted, but I have lots of blog posts coming up over the next month!
A few weeks ago I kicked off a survey on whether you’ve ever tried or had to restore or rebuild master – thanks to all those who responded. The results are below:
The “other” values are:
- 6 x “No neither and I’m ashamed”
- 4 x “Done it years ago but not practiced recently”
- 3 x “I both practice rebuilding and restoring and have also done both in DR scenarios”
- 3 x “I’ve rebuilt master due to collation change”
- 3 x “Moving the other databases to a new server seems easier…”
- 2 x “I once carefully followed the documented procedure for restoring the master database and it failed every time. Currently, I have copies of the master data and log files saved for emergency restorations. Turn sql server off, if it isn’t already, copy the files…”
- 1 x “I work for CSS – I feel like it’s cheating to say “Yep, both restore and rebuild, in a drill and in real life”
- 1 x “I’ve successfully practiced both rebuilding and restoring…but…only after being burned by having to rebuild master on production without backups”
- 1 x “Rebuild master in demo situation”
I’m very pleased to see the results – almost 60% of respondents have successfully restored or rebuilt the master database, either in practice or for real.
For the rest of you, if you’re responsible for recovering SQL Server during a disaster, then IMHO there’s no excuse for either not knowing how to fix master or not having tried it. Here are a few reasons why:
- If master is damaged so that the instance will not start, you can either rebuild master or re-install SQL Server. Which amount of resultant downtime would you rather deal with?
- If you have to rebuild master, you’re going to have to:
- Restore your master backup, or:
- Reattach all databases (do you have a script for that handy?)
- Create all server-scoped objects (do you have a script for that handy too? And, do you know what all the server-scoped objects were before the disaster?)
- Restore your backup of msdb (you have that, right? Otherwise you’ve lost all your Agent Jobs, backup history, SSIS packages,…)
- Restore your backup of model (you have that too, right? Otherwise you’ve lost all the customizations you made.)
- Restore your master backup, or:
- If you have to restore master and you don’t know how, or you don’t have a backup of master, then you have to rebuild master – go to reason #2 above.
You need to practice this.
Restoring master is as simple as:
- Backup master
- Start the server with the -m startup parameter
- Restore your master backup
- The server shuts down automatically when the restore ends
- Remove the -m startup parameter and then restart the server
- If any databases were created after the master backup, reattach them
- If any server-scoped objects were created after the master backup, recreate them
And you’re off and running again.
Steps 6 and 7 can be mitigated with documentation of all changes made to the instance (you all do that, right?) and making sure that a master backup is taken regularly (e.g. every night). I demonstrate it live when I’m teaching and I walk through the steps in a demo in my Advanced Corruption Recovery Techniques course on Pluralsight (Module 5).
Rebuilding master is also pretty simple and involves using the SQL Server installation media to run the setup.exe using the /ACTION=REBUILDDATABASE option (and maybe some others). Full details for SQL Server 2008 onward are in the Books Online topic Rebuild System Databases (and a bit more info in this post from my buddy Bob Ward). After that you’re going to have to walk through the steps in reason #2 above – so you better have backups of master, msdb, and model too. (For SQL Server 2005, you need to use setup.exe too and for SQL Server 2000 you need to use the old rebuildm utility – Google for people’s blogs and videos explaining how.)
Restoring master is not hard. Rebuilding master is not hard. But the very fact that it’s master makes it a bit scary. And rightly so – if you mess it up you may be looking at a re-install. You certainly don’t want to be doing either of these for the first time ever during a real-life disaster recovery situation.
Practice, practice, practice – is the key to successful disaster recovery, no matter what’s involved.
(Check out my online training courses: SQL Server: Detecting and Correcting Database Corruption and SQL Server: Advanced Corruption Recovery Techniques. We can also help you with disaster recovery.)