Restoring the SQL Server Master Database

INTRODUCTION

The master database contains records of the structure/configuration for both the current instance and all other databases. When you run sp_configure, you are writing data to the master database.  It also contains most of the dynamic management views that are necessary to monitor the instance.

The importance of the master database is crucial. First, it has the information necessary for opening all other databases and has to be opened first. Then, it involves all instance level principals for the current instance.

It is crucial to back up the master database daily. Equally important is to know how to restore the master database to the instance. The most frequent cases are the database crash or the necessity to restore the master database to another instance when no longer use the source instance. In this article, we will examine the specific case of moving the master database to another instance. 

CHECK THE STATUS OF APPLICATION SERVICES

Restoring the master database to another instance will involve starting the instance in the single user mode. Thus, it is essential to make sure that the active session is the only one controlling the instance.

For that, stop all application services from accessing the instance, especially if such applications have privileged access. Problems can occur if you start the instance in the single-user mode while the application has already established a session. In this case, you will not be able to proceed with an interactive session from SQL Server Management Studio.

STOP SQL SERVER SERVICES

Stop all SQL Server Services with the SQL Server Configuration Manager: right-click each service and select Stop from the context menu (see fig. 2).

Figure 1. Stopping SQL Services

START SQL SERVER IN THE SINGLE USER MODE

To restore the master database, you need the SQL Server instance in the single user mode. Take the following steps:

1. Open CMD Prompt
2. Navigate to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
3. Issue sqlservr –m OR NET start MSSQLServer /c /m /T3604

Figure 2. Starting SQL Server in Single user Mode

OR

Figure 3. Starting SQL Server in Single user Mode

Note that step 2 refers to the Binn directory location. It can be different in your installation.

You can find the necessary directory by checking the Service tab of SQL Server service properties in SQL Server Configuration Manager (See Figure 4):

Figure 4. Checking the SQL Server Install Path

RESTORE MASTER

When you restore master from one instance to another, you get new principals and new values in system catalogs containing such values as the instance name and even the tempdb location.

Before restoring master, ensure that the destination server’s paths are available for TempDB files as defined in the source server. Otherwise, the new instance will not start up successfully after the restore is complete.

1. Open SQL Server Management Studio
2. Run the code in Listing 1 in SQL Server Management Studio

-- Listing 1: Restore Master Database

restore database master from disk ='' with replace;

Figure 5. Restoring Master

NB:  The instance will shut down once the restore is complete.

SERVICE ACCOUNT CHANGE

To start up SQL Server after the restore is complete, do the following:

1. Change the SQL Server Service Account to the currently logged on account.
2. Open Start > All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager
3. Right-Click each service and select Properties
4. In the Log On tab, enter the desired Account Name and the corresponding password.

Figure 6. Changing Service Account

SERVER NAME

The master database contains the name of the instance where it is located. Since the backup restored in this scenario is from a different instance, you need to update the server name using stored procedures, as shown in Listing 2.

-- Listing 2: Change the Instance Name for a SQL Server Instance

-- Check the current server name

select @@SERVERNAME

-- Change the server name as seen by the database

sp_dropserver 'EUK-POSTSVR-01'--Present Server name goes here

go 

sp_addserver 'EUK-POSTBKP-01','local'--New Server name goes here 

go

Restart SQL Server from SQL Server Configuration Manager for the name change to take effect.

SERVICE LOGINS

The master database also records all logins associated with the source instance. In the new instance, you need to clean up unnecessary logins. After that, you can add the local default SQL Server Groups to the current instance.

-- Listing 3: Remove Unnecessary Logins

drop login [EUK-POSTSVR-01\SQLServer2005MSSQLUser$EUK-POSTSVR-01$MSSQLSERVER]

drop login [EUK-POSTSVR-01\SQLServer2005MSFTEUser$EUK-POSTSVR-01$MSSQLSERVER]

drop login [EUK-POSTSVR-01\SQLServer2005SQLAgentUser$EUK-POSTSVR-01$MSSQLSERVER]

-- Listing 4: Add local default SQL Server Groups

create login [EUK-POSTBKP-01\SQLServer2005MSSQLUser$EUK-POSTBKP-02$MSSQLSERVER] from windows;

create login [EUK-POSTBKP-01\SQLServer2005SQLAgentUser$EUK-POSTBKP-02$MSSQLSERVER] from windows;

create login [EUK-POSTBKP-01\SQLServer2005MSFTEUser$EUK-POSTBKP-02$MSSQLSERVER] from windows;

NB: Ensure that the server and agent service accounts belong to their respective groups on the OS level and that these OS groups have the required permissions. Table 1 shows permissions for SQL Server Service accounts.

Table 1. OS Privileges Required by SQL Server Service Accounts

Once the housekeeping is complete at the database level, revert to the regular service account. Restart the instance one more time from the SQL Server Configuration Manager.

At this point, you can start the application services.

CONCLUSIONS

During the execution of the above scenario, errors can take place. Below, you’ll see the information about these errors.

If you troubleshoot the issues, you might use tools like SysInternal’s ProcMon. They isolate the file permission issues when starting SQL Server (See Figure 7).

Figure 7. Checking Permission Issues with ProcMon