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.

HOW TO: Recover the Master Database in SQL 2012

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:

HOW TO: Recover the Master Database in SQL 2012

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

HOW TO: Recover the Master Database in SQL 2012

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:

HOW TO: Recover the Master Database in SQL 2012

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:

HOW TO: Recover the Master Database in SQL 2012

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

HOW TO: Recover the Master Database in SQL 2012So, 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:

HOW TO: Restore the Master Database in SQL Server 2012

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.

25 thoughts on “HOW TO: Restore the Master Database in SQL Server 2012”

  1. My new years resolution as a mgr is to give my team a monthly sandbox challenge. We then debrief it over lunch (on me of course). This just made the list. I love it when the “big brain blogs” still pound home the fundamentals. Good stuff.

    Reply
  2. I would like to add one step to the checklist.

    When disaster really strikes, you will not be able to start the SQL Server instance due to the corrupt or missing master database. In many cases the first step will be to rebuild the default master database. The process is described in this article, http://technet.microsoft.com/en-us/library/dd207003.aspx.
    Then you can start SQL Server in maintenance mode and restore your latest backup of master.

    Reply
    • Vemund,

      I was thinking about doing the rebuild of master in a 2nd post, thanks for the comment, I will consider that one vote for me writing the post!

      Tom

      Reply
  3. sqlservr.exe file name misses the second ‘e’ because at the time it was invented, the use of 8.3 naming convention was widespread and long names might not even be available at all.

    Reply
    • Yep, that’s what I imagined would be the case. And for most people who are too young to know what 8.3 naming means, they are likely to misspell that filename in the middle of the night.

      Reply
      • Sybase is using sqlsrvr.exe – ‘e’ letter could be added to differentiate binary name when MS and Sybase parted their ways in July 1993 (?).

        🙂

        Reply
  4. In case anyone else runs in to it, I’ve had problems with the -(dash) switch indicator. For example, the command to start SQL Server in single user mode would throw basically a blank popup window with nothing to indicate a problem. Same when starting SQLCMD. Changing the – to / sorted that out, but why I have no idea. Usually – and / are the same when it comes to this product/switches. OS-centric perhaps?

    Reply
  5. Was not able to start SQL in single user mode. I copied my backup file to another 2012 SQL Server and followed the instructions to restore the master database to that server. I then copied the master.mdf and mastlog.ldf files back to the server with the corrupt master database. I was then able to start SQL in single user mode. After that I restored the master database to that local machine. Rebooted and everything is up and running. Thanks a bunch!

    Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.