Restoration of Master database


I personally believe that a Database Administrator spend most of his time with Backup & Restore. Most of us are aware of how to backup system and user databases? In my previous posts, I have discussed about how to backup and restore an user database. As a database administrator, we should aware of how to restore the master database (only when required as we don’t restore the master database frequently). As I was creating a test server on SQL Server 2008, I thought of writing this post for future reference.

Note: If you want to practice the restoration of the master database, I suggest to use a test machine. Please don’t restore the master database on a production environment unless its required.

Before we restore the master database, the SQL Server service must be restarted in Single user mode. Restoration of the master database can not be performed while SQL Server service running in default mode. Also make sure that you have a valid master database backup available from which you are going to perform the restoration. Let’s start the restoration procedure.

1. Make sure you have a valid master database backup. To test the restoration is working properly, I have added a new login to the SQL instance after completing the full database backup of master database. Look at the below screenshot:

image 

Once we restored the master database, the above logins will not exists on the SQL Server instance.

2. Open SQL Server Configuration Manager (Start –> All Programs –> Microsoft SQL Server 2008 –> Configuration Tool).  Right click on SQL Server Services and go to Properties.

image

3. On the SQL Server Properties page, click on the Advanced tab. On the Startup Parameters, add the following ;-m to the end of the value to start the service in Single user mode.

image

4.  Click on the Apply button, a pop-up message will appear on the screen, which informs us that we need to restart the SQL Server Service to make the above changes effective.

image

5. Restart the SQL Server service. Open the command prompt and connect to the SQL instance using SQLCMD. To connect to the default instance of a SQL Server using Windows Authentication, use the following syntax:

SQLCMD -S <ServerName> -E

where -S stands for the SQL Server instance and -E stands for Windows Authentication. For a complete list of switch, use the command  SQLCMD /?

6. As the SQL Server service is started in Single user mode, only one connection can be available to the instance. For this reason, I will be using SQLCMD to connect to the instance and complete the restoration. Once you connected to the SQL instance using SQLCMD, execute the database restoration command, in this case, the command is:

   1:  RESTORE DATABASE [master] FROM DISK = 'C:\Backup\master.bak';
   2:  GO

 

Look at the following screenshot:

image

7. Remove the ;-m from the Startup parameters of the SQL Server Service properties and restart the SQL Server service. Log on to the instance and verify.

image

If you compare the above screen shot with the first screen shot of this post, you will find that the windows group sunita\SQL_DBA is not present on the instance after the restoration as the login was created after the full database backup.

8. Execute DBCC CHECKDB on the master database to check that the database is not corrupt.

This completes the demo for the master database restoration.

Notes: In real world, the restoration of master database is not as simple as mentioned in this post, however, the basics remain the same. Some more steps can be added depending on the scenarios. The idea behind writing this post is to practice the master database restoration on the test environment.

Advertisements

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in Disaster Recovery. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s