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.

Filed under: Backups, Microsoft SQL Server, Problem Resolution, SQL Judo's Monthly DBA Challenge, SQLServerPedia Syndication, System Databases Tagged: backups, microsoft sql server, SQL Judo's Monthly DBA Challenge, system databases