Disaster recovery 101: restore master or rebuild master

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:

rebuildm

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:

  1. 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?
  2. If you have to rebuild master, you’re going to have to:
    1. Restore your master backup, or:
      1. Reattach all databases (do you have a script for that handy?)
      2. 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?)
    2. Restore your backup of msdb (you have that, right? Otherwise you’ve lost all your Agent Jobs, backup history, SSIS packages,…)
    3. Restore your backup of model (you have that too, right? Otherwise you’ve lost all the customizations you made.)
  3. 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:

  1. Backup master
  2. Start the server with the -m startup parameter
  3. Restore your master backup
  4. The server shuts down automatically when the restore ends
  5. Remove the -m startup parameter and then restart the server
  6. If any databases were created after the master backup, reattach them
  7. 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.)

7 thoughts on “Disaster recovery 101: restore master or rebuild master

  1. Something I recommend everyone should use – it’s quite simple to set up:

    http://scriptsqlconfig.codeplex.com/

    Create a batch file with one line per instance as follows:
    \ScriptSqlConfig /server /dir “”

    Schedule it to run nightly – it will save you a ton of headaches in the event you do have to remember some of those “server-scoped objects” like credentials, DB Mail config, linked servers, logins, sp_config output, alerts, jobs, operators, etc.

    I don’t believe it works with SQL 2012 yet – if there is a version for 2012, please share it.

    This is only to augment Plan B – Plan A is obviously to restore master.

  2. I tried testing this on my local test environment, which is just my laptop. I have a SQL Server 2008R2 named instance. I backed up the Master database and then started the SQL Server instance in single user mode. Here is where things go wrong. Now I can’t connect to the instance anymore. From SSMS I get “login failed for ‘domainname\lmarkum’. Server is in single user mode. Only one administrator can connect at a time.” I tried the info here, http://sqlserver-help.com/2013/12/06/help-how-to-fix-error-reason-server-is-in-single-user-mode-only-one-administrator-can-connect-at-this-time/, and here, http://www.practicalsqldba.com/2012/08/sql-server-lost-all-administrator.html with no luck. I added ;-mMicrosoft SQL Server Management Studio

    1. Turn off SQL Agent and disconnect Object Explorer – they will likely grab the only connection. If all else fails, turn off SQL Agent and connect using SQLCMD.

  3. Here is a trick I use. Once SQL Server is installed and ready to restore the MASTER database do the following before starting the MASTER database restore.

    1. Read the header from the MASTER backups to ensure you are at the right version and patch levels
    2. Stop SQL Server services
    3. Copy the master.mdf and master.ldf files to a new folder or somewhere else
    4. Start SQL Server services
    5. Perform your master restore
    6. If restore goes bad, stop SQL Server services, copy back the MASTER database files from your new folder overwriting the existing ones and restart SQL Server services. This will eliminate the need to rebuilt and/or reinstall SQL Server
    Now see what the errors are and try again.

    Thanks,

    Rudy

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.