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:
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.
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.
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.
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';
Look at the following screenshot:
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.
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.