How to Recover 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 need to restore the master database as a result of a disaster. In other words, this isn’t something you will find yourself needing to do very often. But when the times comes (in the middle of the night, of course) you will want to be prepared to do the following steps. Restoring the master database is surprisingly easy. Of course, I am assuming you have a backup. You are running backups, right? Good. Assuming you have a good backup of master to use, the process I use is as follows:

1. Stop instance
2. Open command prompt
3. Run –m, DO NOT CLOSE WINDOW
4. Open sqlcmd
5. Restore master from file WITH REPLACE (Note: Windows will close, do not panic!)
6. Restart instance

That’s all there is to it. I would have my team practice this every now and then on a test server 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.

Recover the Master Database in SQL 2012

First up, let’s take a backup of the master database:

BACKUP DATABASE [master] TO  DISK = N'C:SQLBackupsmaster.bak' WITH INIT
GO

OK, now we will create that dummy login as part of our practice run. Here’s a simple enough script to do that, complete with a check that the login didn’t already exist before we started:

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

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

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

OK, next up we will stop the instance. I’ll just use the default PowerShell command window: Now we need to 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 FilesMicrosoft SQL ServerMSSQL11.JAMBONMSSQLBinn, probably not the same for you). Once there, I will run the following command:

.sqlservr.exe –c –m –s JAMBON

If you are using the default instance of SQL Server, then you only need to run:

.sqlservr.exe –m

But 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. Next, I will open up a command window (running as administrator) 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 using:

RESTORE DATABASE master FROM DISK = 'C:SQLBackupsmaster.bak' WITH REPLACE

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

  The PowerShell window will reflect that the instance has been shut down: I will use SQL Server Configuration Manager to restart the instance. Once it is started, I will open up SSMS and run the following query:

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

And then, verify that the login is no longer there. Congratulations! You have just restored your master database, and now you can go about with whatever else you need to get done to recover from whatever disaster struck.