HOW TO: Restore the Master Database in SQL Server 2012

In my seven years as a production DBA I can count on one hand the number of times I have needed to restore the master database as a result of a disaster. With over 180 instances of SQL Server in our care I believe that is a large enough sample size making it safe to assume that restoring master is not something you will find yourself needing to do often. But when the time comes (in the middle of the night, of course) you will want to be prepared.

The master database stores metadata about the instance of SQL Server: things like server logins, databases and database file locations, server configuration details, and linked server definitions are all stored inside of the master database. If the master database is not available (say, due to corruption or a disaster that wipes away the disk where the master database is kept) then your instance of SQL Server cannot function.

So, yeah, it’s kinda a big deal as databases go. When the time comes you will want to be able to restore the master database quickly.

Restoring the master database is surprisingly easy. Of course I am assuming you have a backup. You are running backups, right? Good. If you aren’t running backups then you need to get started. Here’s a link to the scripts that SQL experts use the world over, made by SQL Server MVP Ola Hallengren.

Assuming you have a good backup of master you can test the restore process by following these simple steps:

1. Using SQL Configuration manager, stop the SQL Server instance
2. Open a command window
3. In the command window, start the instance executable in maintenance mode
4. In a second command window, open SQLCMD
5. Restore master from within the SQLCMD window
6. Using SQL configuration manager, restart instance

That’s all there is to it. I would have my team practice this on a test server a few times a year just to make sure their skills were sharp should they ever need to restore master when half-asleep in the middle of the night. As part of the test I would have them create a SQL login at the beginning. That way when the restore was complete they could verify that the login disappeared.

Let’s walk through it together.

Restore the Master Database in SQL Server 2012

First up, let’s take a backup of the master database. I’m doing this as part of an exercise here, you will want to make certain that if you take a backup of master on a server in your environment that you aren’t interfering with any existing backup plan:

BACKUP DATABASE [master] TOÂ  DISK = N'C:\SQL\Backups\master.bak'

Next we will create a test login as part of our practice run:

USE [master]
 GO
 CREATE LOGIN [master_restore_test]
 WITH PASSWORD=N'test',
 DEFAULT_DATABASE=[master],
 CHECK_EXPIRATION=OFF,
 CHECK_POLICY=OFF

Just for fun, we will create a new database as well:

CREATE DATABASE [restore_test]

Since the login and database were created after the backup they won’t be there after the restore is complete.

Let’s get the restore process started. First we must stop the instance. I will use SQL Configuration Manager to select the instance and then hit the red stop button:

Next we will restart the instance using the –m startup parameter from a command window. I will navigate to the directory where the sqlservr.exe resides (for me that is C:\Program Files\Microsoft SQL Server\MSSQL11.JAMBON\MSSQL\Binn, probably not the exact same for you, but close). One there I will run the following command:

.\sqlservr.exe –c –m –s JAMBON

Since I am using a named instance (JAMBON), I need to include the –s switch followed by the name. The –c switch allows for faster startup time when SQL Server is not being run as a service. You can read more about the available switches here. [PRO TIP: That’s not a typo, the sqlservr.exe executable is missing the second ‘e’. No, I don’t know why. I’m guessing someone will tell me this is how you lived back in the MS-DOS days before Y2K scared the hell out of everyone. But trust me, you’ll only misspell it a few dozen times in the middle of the night before you figure it out.]

This is what you will see after it has started:

Next, I will open up a command window and connect to my named instance using:

SQLCMD –S .\JAMBON

You can read more about SQLCMD here. After I connect to the instance, I can restore the master database:

RESTORE DATABASE master FROM DISK = 'C:\SQL\Backups\master.bak' WITH REPLACE

Note that once the restore is complete the instance will be shut down. It looks something like this:

The first command window will also reflect that the instance has been shut down:

So, let’s get the instance restarted!

I will use SQL Server Configuration Manager to start the instance and once it is started I will open up SQL Server Management Studio and run the following query:

SELECT *
 FROM sys.server_principals
 WHERE name = 'master_restore_test'

And verify that the login is no longer there.

The database is gone as well. However, the underlying database files still exist. Remember, master only stores the metadata about your instance. The restore didn’t erase the database files, it just reset the instance to a point in time before the database was created. This is important because if you find yourself in a disaster recovery situation you will want to understand why you may not be able to access your databases after restoring master.

To get our test database back you can either do a restore, or an attach. Here’s the syntax for the attach:

USE [master]
 GO
 CREATE DATABASE [restore_test] ON
 ( FILENAME = N'C:\SQL\Data\restore_test.mdf' ),
 ( FILENAME = N'C:\SQL\Logs\restore_test_log.ldf' )
 FOR ATTACH
 GO

Now, the more likely disaster scenario would be that the restore of master has the metadata for the databases but the database files don’t exist. In that case, SQL Server 2012 will show the database in the state of “recovery pending”, like so:

You can’t attach the database in this case, because master thinks it is already there. And, in a way, it is. But it’s more like a ghost than anything else. The easiest thing here is to do a restore. In fact, that’s the method I chose, and I even had scripts ready to run in the event of a disaster that would restore all of the databases dynamically. But what if you don’t have backups (yeah, that does happen). Well then, you can drop the database, then attach.

But if you don’t have backups, and you don’t have the data and log files, then you WILL have plenty of time to update your resume before leaving town.

Congrats, you have just restored the master database in SQL Server 2012. Take your time and practice this. Try different disaster scenarios and see what error messages get generated. You can learn a lot about recovery methods in a short amount of time just by practicing a master database restore.