Restore SQL Server Master Database Options

By: Eric Blinn   |   Updated: 2019-11-18   |   Comments (3)   |   Related: More > System Databases

Problem

I need to restore a backup of my master SQL Server database because there is a problem with mine. What are the steps?

Solution

The SQL Server master database cannot be restored like other user or system databases because SQL Server cannot execute without an active master database.  While it is rare to need to restore a SQL Server master database, if that need arises it is imperative that a DBA be prepared for the situation.  This tip will explain the situations where a master database might need to be restored and how to complete the steps correctly.

When would one have to restore a SQL Server master database?

The number 1 reason a master database needs to be restored is corruption.  If the master becomes corrupt the best course of action is generally to restore from a backup.  The data within master is relatively static so having to lose a few hours of history should not be a big deal in most scenarios.

Other reasons to restore master might be to recover a dropped login or a password that was changed and cannot be reset for some reason.  If the master database needs to be restored to recover user data or objects then this would be an opportunity to move them out of master and into a user database.

Two methods to restore SQL Server master database

There are 2 available methods to restore a master database.  The first can only be used to restore a master database on a running SQL Server.  If the issue is master database corruption and this corruption causes SQL Server to refuse to start, then the second method must be used.  The second method can optionally be used if SQL Server is running.

First let's take a backup of the database that we can use to restore.

backup database master to disk = 'c:\master.bak' with compression;

Restore SQL Server master database when SQL service is able to start

This method uses a traditional database restore command with one twist.  Attempting a traditional restore during normal SQL Server operation will result in an error, but that error is really useful.

restore database master from disk = 'c:\master.bak' with replace;

The server must be stopped and started in single-user mode.   This is accomplished by stopping and restarting the service from a command prompt running with enhanced permissions.

In the command prompt stop the service and restart it using the net stop and net start commands and the /m parameter to force single user mode.  The code below is for a default instance.  If running a named instance modify the "MSSQLSERVER" with the full service name.

C:\WINDOWS\system32>net stop MSSQLSERVER
The SQL Server (MSSQLSERVER) service is stopping...
The SQL Server (MSSQLSERVER) service was stopped successfully.
 
 
C:\WINDOWS\system32>net start MSSQLSERVER /m
The SQL Server (MSSQLSERVER) service is starting.
The SQL Server (MSSQLSERVER) service was started successfully.			

The service will be in single user mode meaning exactly 1 SPID can connect to the instance.  It is very important that the one available connection be held by the person attempting the restore.  Through normal operation any one person with a SSMS window open would have many SPIDs connected.  Avoid this by attempting to connect with exactly 1 new query window and do not also attempt to have the Object Explorer open as that will attempt to use the connection.

After creating a connection, attempting to log in to the Object Explorer or a second query window will result in this error.

Now that the service is started and the new query window is connected, the restore that previously failed should work.

At this point the SQL Server service will be stopped.  It can be restarted using either the command prompt (without the /m parameter) or using any other common method such as the services window or SQL Server Configuration Manager.

The first step upon brining the server back up should be to run a DBCC CHECKDB against the master database to confirm that any corruption has been fixed.

Restore SQL Server master database when SQL service is not able to start

If the service won’t start how can the restore command be executed?

This is a tough problem.  Not one that this author hopes any reader will experience, but if it does happen these are the steps to follow to complete the restore.

Step 1

Find another instance of SQL Server of the same major version of SQL Server.  Perhaps a development box would work.  If there isn’t another available then quickly installing an Express or Developer Edition instance may be in order.  In the event that this approach is being chosen optionally and the service will start, then the failing instance can be used as if it were the secondary instance.  This demo will use the primary instance as if it were the secondary.

Step 2

Restore the master database backup to the working SQL Server instance as if it was a regular user database.  An alternate name will need to be used, e.g. master_recovery.

restore database master_recovery from disk = 'c:\master.bak' with
move 'master' to 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master_recovery.mdf',
move 'mastlog' to 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\mastlog_recovery.ldf';

Step 3

Now that the database is online, step 3 is to detach that database from the working instance.

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'master_recovery'
GO

Step 4

At this point the 2 database files are no longer attached to the working instance of SQL Server.  Step 4 is to move the 2 files to the server hosting the instance experiencing the problem with master.  If this method was chosen optionally and the restore was done on the target instance then the files are already there!

Step 5

Next, for step 5, the instance of SQL Server experiencing the problem must be told to consider these new files instead of the failing files.  This can be accomplished by changing the startup parameters of SQL Server to point at these new files or to rename master.mdf and mastlog.ldf to new names, and rename the _recovery files to match the default file names.  Either way, the next service start will use the new files rather than the bad files.

For this demo the startup parameters were changed to point to the _recovery files instead of the default files.  Next the service was restarted (since it was running).  Now the service is using the recovered master database instead of the failing master database.

The last step is to run DBCC CHECKDB on this copy of master.  If this attempt is corrupt, go back further in time until a good copy is found.  The CheckDB cannot be run during an earlier step because a master database can produce false negatives when checked as a user database.

What data is lost when restoring a SQL Server master database?

Having just moved back in time by restoring an older master database or getting a copy from another instance, what data could have been lost in this transition?

• Most data in the master database is static, but going back in time a few days there are a few things to check.
• Were any logins added or dropped between the backup and restore?  These would need to be recreated or dropped. 
• Did anyone change their password during that window?  If so, their old password would be back in force.
• Were any server role assignments changed?  These would be reverted.

If any databases were added during the time window then they will not be recognized by the old master database. The database files themselves would not be dropped so it would just be a matter of attaching the database.  If any databases were dropped during that window then they will appear as suspect databases that can just be dropped again.

Any settings that were changed like those from sp_configure would have reverted.

Next Steps

• Learn more about master from Books Online
• Move the master database
• Start an instance with only a master database
• Cleaning up a cluttered master database
• SQL Server Master Database Tutorial

About the author

Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips