Problem
While researching this article I was surprised by the number of DBAs who back up their user databases, but not their system databases. They either do not fully understand the importance of these databases or have been lulled into complacency by years of smooth sailing. Whatever your reason may be, I’m going to show you how to save yourself (mostly) should you ever find yourself with no viable master database and no good backup.
Solution
There are several reasons you may need to recover your master database. You may have removed something critical and you want it back- a login, linked server or some other system object. This may be the easiest scenario because you still have a master database to start from. Your master database may have been damaged due to hardware or software failure and rendered unusable. You may be restoring to a brand new server or creating a clone of your instance.
I’ll cover three scenarios in this tip.
- First, you have a master database that is viable and you have a backup, all you want to do is a little “time travel” to get back to a known state.
- Second, your master database is gone or unusable, but you have a backup from which to restore – thank your lucky stars.
- Last, your master database is kaput and you have no backup to use for recovery. Don’t get out your resume just yet, we can get your instance back up and running, but there will be a lot of work to do after that to return to some semblance of what it once was.
Warning! If your master database is all that’s broken STOP NOW and make copies of your msdb and model database files! Some of the instructions below will overwrite them destructively. Let’s not add insult to injury by whacking stuff that is still good.
Scenario 1: Restoring an existing SQL Server master database from backup
Restoring master is tricky because it contains the information about all the other databases in the instance and is required for startup. In the case where you have a “good” master database to start from you have to start in single-user mode using the -m flag. In SQL Server Configuration Manager, right-click on the SQL Server service, select properties, then the startup parameters tab. Enter -m in the top box and click add, then apply, then restart SQL Server.
Now, with SQL Server in single user mode, using the command line (SQL Server Management studio won’t run without in single user mode!) you can recover your master from backup. Find sqlcmd.exe in your …/Tools/Binn directory and run it. You will need to use the -S flag if you have a named instance and the -U and -P flags if you’re not using a trusted connection (see BOL for more information). From there you can restore from your backup just like any user database.
That’s it, now remove the -m from the SQL Server service startup options and restart the SQL server service in multi-user mode! Detailed master database restore instructions are on MSDN HERE and there is a very good series of tips on MSSQLTips.com HERE to help you prepare for a master database rebuild.
Scenario 2: Rebuilding then restoring the SQL Server master database from backup
If you don’t have a viable master database you need to create one first, if your master database is there and you just want to recover from backup use scenario one. There are two ways you can rebuild your master database. For 2005, 2008 and 2008R2 you must use setup to recreate ALL of your system databases at once, for 2008R2 and later you can use the template option to overwrite just the master database.
Using Setup to Recreate System Databases
From the bootstrap directory- “C:\Program Files\Microsoft SQL Server\<nnn>\Setup Bootstrap\<release>” (replace <nnn> with the right version and <release> with the right release) run the following command substituting your instance name, a windows account to have administrative rights and a secure password for SA where indicated. This is where you want to make sure you have copies of your good model and msdb databases saved off somewhere safe!:
.\setup /ACTION=REBUILDDATABASE /INSTANCENAME=<instance_name> /SQLSYSADMINACCOUNTS=<admin_account> /SAPWD=<sa_password>
For Example:
Now that you have a master database to work with you can recover it from a backup if you have one. At this time you should also shut down the services and replace the blank msdb and model database files with the copies you saved off previously. Jump to scenario three to find out how to recover at least some of your master database’s data if you’re not recovering from a backup.
Using the Template Master Database
For 2008 R2 and 2012 the system database recovery function works differently. Rather than using T-SQL scripts there are template databases created at install time, which are copied over the database and transaction log files of your system databases by the setup.exe /REBUILDDATABASE option. If your msdb and model databases are okay then it may be simpler to manually copy the template files of the master database to where they belong rather than rebuild all three system databases and restore the msdb and model from backup. The template files are found in the “C:\Program Files\Microsoft SQL Server\MSSQL<version>.<instance_name>\MSSQL\Binn\Templates” directory depending on your instance name and version.
If you have a good backup of your master database you can now restore it using the instructions from scenario one. If you don’t have a backup then keep reading to see how you may be able to get back some of the data you’re missing. Start your instance and follow along as we try to get stuff put back together.
Scenario 3: Reconstructing un-backed up data from the SQL Server Master Database
First, if you skipped the first sections of this tip, follow the instructions above to re-create your master database making sure to keep good copies of your other system databases in case they get overwritten by the rebuild.
At this point you should have an instance up and running, if you have master database backups and have restored then congratulations, you may breathe a sigh of relief and go on to the next tip. If you had no backups then we still have some work to do.
Reconnecting the databases
Connect to your server using SSMS and the Admin account you used when it was installed or that you used on the rebuild step above. The first thing you will notice is that when you expand the databases tree it is empty. This is because master contains all the database location information and its now gone. But the databases are still out there, we just need to tell SQL Server where they are. If you have backups of these databases the easiest solution is to use them now, if you don’t then this will take some legwork as you will need to record the location of all the database and transaction log files for each database and attach them manually using the either the following T-SQL code with the right database name and file names replaced or SQL Server Management Studio’s attach database command from the database menu:
USE [master]
GO
CREATE DATABASE [UserDatabaseName] ON
(FILENAME = N'C:\sqldata\data.mdf'),
(FILENAME = N'D:\sqltlog\tlog.ldf')
FOR ATTACH
GO
Right click the databases tab and select “Attach …” then click “Add” on the Attach Database dialog. By selecting the mdf file for each database it will find the .ndf and .ldf files (if they have not been moved).
Note that you may need to change the ownership of the databases, they will be owned by the account that re-attached them. You will need to recreate the logins first!
alter authorization on database::[database_name] to "owner_name"
Restoring other system objects
There are several other object types that exist in the master database that will be lost when you rebuild or restore from template and don’t have a backup; logins, endpoints, and linked databases to name a few. This is where the rest of the restore gets either really tedious or impossible, but look on the bright side – you’ll be ridding yourself of all that baggage that has been accumulating for years. If similar objects exist in other instances (your QA or development environments, for example) you can use SSMS to make create scripts to edit and deploy on the restored server. Even if the objects are not an exact match, the scripts will be close enough to save you a lot of needless typing.
Next Steps
- First and foremost, without further delay, create and implement a backup plan for your system databases! It’s as simple as setting up a maintenance plan. Once you’ve got that setup, take a look at your backup plans in their entirety (you do have them, right?) and review them with this experience fresh in your mind.
- Are your system databases backed up frequently enough to meet your recovery point objectives?
- Are you user database backups actually running and will they met your RPO?
- Where are the holes, if any, that you want to fill and what will it take to fill them?
- If you’re not backing up due to budget constraints you need to make a business case for a tape drive or external drive of some sort, they are far cheaper than lost business due to a prolonged outage or worse- lost data.
- Backup and recovery are a core skill of any DBA (on any platform) make sure you have backups and make sure you regularly test not only your backup media, but your processes and procedures as well. Crunch time with your CIO looking over your shoulder is not the time to verify that you recovery procedure it 100% up-to-date!
