SQL Judo’s Monthly Challenge – January 2014 – Restore the Master

5

January 5, 2015 by Kenneth Fisher

Every month SQL Judo (Russ Thomas) (b/t) challenges us to do his Monthly DBA Challenge. I’ve decided it would be fun (and good practice) to start doing them and blog about it. Another major benefit is that it will force me to move out of my normal comfort zone. For example in the last year he has had challenges on Powershell and Heketon, neither of which I’ve worked with yet.

I’m going to start at the beginning of 2014 and work my way forward. No promises I’ll hit them all and once I catch up of course I’ll be doing them about once a month. I’m going to suggest you try them yourself and then come back and read my attempts. Here is my first attempt:

January 2014 – Restore the Master

Restore the master database on a server with at least one user database. Bring the server back online with all settings, user databases, logins, rights, and behaviors intact.

I’ve actually had to do this one before under several circumstances. There are a number of possibilities. For example:

  • You have a good preferably recent backup and are going to restore it back to its original server. This is the simplest case.
    1. Start the server in single user mode.
    2. Restore the master database using the WITH REPLACE clause. You will have to do the restore in a query window or SQLCMD. Object Explorer would require at least two connections. One for Object Explorer and one for the Restore GUI.
    3. Restart the server in multi-user mode.
  • You have a good backup but it’s being restored to a different/new server. This is a common DR situation when you are having to completely rebuild the machine. This is basically the same process as the previous step. The most significant difference is that you have to be worried about build and version level. While there are some workarounds the best thing is to make sure your backup and SQL Instance are the same build and version.
  • Last but not least, you’ve lost your master database completely. Your backup is corrupt (or you never took one) and there is nothing to restore. At this point you will need to Rebuild the master database. There are a fair number of moving pieces to this so I recommend following the link and reading it in detail. But basically to do this you will need the install media and run setup with /ACTION=REBUILDDATABASE. This is going to rebuild ALL of the system databases so make sure you have backups. It’s also going to wipe out your settings, logins, user databases etc. Everything about master will be gone so make sure everything is scripted and/or backed up.

 
In all cases remember that among other things server principals, server permissions, connected databases and some server settings are stored in master. Any changes to these settings since the backup (or all of them in the case of a rebuild) that you want to keep will need to be corrected.

  • Server Principals and Permissions – The best thing here is to script these out and run the script in the new location. You can use my sp_SrvPermissions or any other method. In some cases you will also want to check the databases for miss-matched SIDs (Security IDentifier).
  • Connected Databases – This is mostly a problem with a complete rebuild where all of the attached databases are gone. When you do the restore if the database files are already in the place that master expects them to be then it won’t be a problem otherwise you can simply restore or attach them.
  • Server Settings – Check the contents of sys.configurations and the expected location of each of the system databases.
  • Build – If you rebuild the system databases they will be set back to the base build and you will need to apply any patches needed to bring it back up to the original build level.
  • Encryption – All encryption on the instance is based on the Service Master Key. You will need to have a backup of this and restore it once you have restored master. You do this using the BACKUP SERVICE MASTER KEY and RESTORE SERVICE MASTER KEY commands. Even if you don’t think you are using encryption on your system you probably are. The passwords in the security section of Linked Servers for example are encrypted using the Service Master Key
  • for example.

 
 
Now here is my “You should never do this except in an emergency but here’s how” section.

  • You have a good backup but your instance won’t start so you can’t do a restore. You don’t even have the install media to create a blank set of system databases. You do however have another working instance available of the same version and build (another, older, version or different build might work but it’s more questionable). Restore the master database as master_other (just a regular restore, no special effort needed) then detach it. Copy the database files (mdf & ldf), and re-name them, to location(s) that your other instance expects and bring it up.
  • This time you don’t even have a viable backup. Still no install media, and the instance won’t come up. You do however still have that other running instance. Shut the running instance down, copy the master database files to the location expected by the down instance and bring it up. Then also bring up the running instance. At this point you are going to have some issues. Your server principals, attached databases, etc are all going to be completely different and you will need to take some time to fix them.

Again please remember these are emergency only methods when nothing else will work. Not recommended.
 
There you go. My first attempt at answering one of SQL Judo’s monthly DBA challenges. How do I you think I did? Did I miss anything? Get anything wrong? If so please feel free to let me know in the comments. Next will be February 2014 – Kill with the DAC. And to make it more fun, he wants us to use SQLCMD.

5 thoughts on “SQL Judo’s Monthly Challenge – January 2014 – Restore the Master

  1. Rudy says:

    Hello,
    Just want to pass on a better way of rebuilding the master database. Instead of running the installation perform the following:
    1) Before starting any restore of the system databases like master, stop SQL Server service and make a copy of the Data folder (or wherever the .mdf and .ldf files are located) and restart SQL Server services
    2) Restore the master database
    3) If error occurs, stop SQL Server services and copy the .mdf and .ldf files back to the original location, overwriting the corrupted files
    4) Start SQL Services and then try the restore again

    I wrote a blog about this
    http://sqlsurgeon.blogspot.ca/search?q=rebuild

    Oh and don’t forget to restore the MSDB database too 🙂

    Hope this helps,

    Rudy

  2. I’ll admit I suggested something similar in my “emergency only” section. However here are a few problems. Any new logins, password changes, server level permissions, patching, new databases etc between the time of your “copy” of master and your “restore” are going to be lost unless you are making regular “copies”. That of course will require regular downtime of your instance which frequently isn’t an option. You also have no way to test your “backup” copies until you actually try to use them. If something went wrong during the copy you are messed over. True backups have an option to verify, which while not perfect is better than nothing. Also your suggestion to copy the user databases while doing all of this on the surface seems like a good one but if your databases are hundreds of GBs or even worse TBs your instance could be down for quite a while. And again you have no way to verify that these copies are any good.

    Again while a good workaround at need it’s generally considered best to use regular backups for DR purposes.

  3. Ken Howe says:

    The main issue we face once SQL Server is put into single user mode is assuring that another account doesn’t grab the one allowed connection first, thereby making it impossible to restore the master database. Any suggestions on how to overcome this?

    • I just read a suggestion from Thomas LaRock that said start the server from the command line and then connect using SQLCMD. Also I’m fairly sure that you can connect using the DAC even if the single connection is used up. Then you can see who they are and kill that connection.

  4. […] SQL Judo’s Monthly Challenge – January 2014 – Restore the Master […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,664 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: