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):

#programming #sql server #how to #sql server #sql server master database #database