For this reason, I will be using SQLCMD to connect to the instance and complete the restoration. As the SQL Server service is started in Single user mode, only one connection can be available to the instance. For a complete list of switch, use the command SQLCMD /?Ħ. Where -S stands for the SQL Server instance and -E stands for Windows Authentication. To connect to the default instance of a SQL Server using Windows Authentication, use the following syntax: Open the command prompt and connect to the SQL instance using SQLCMD. 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.ĥ.

On the Startup Parameters, add the following -m to the end of the value to start the service in Single user mode.Ĥ. On the SQL Server Properties page, click on the Advanced tab. Right click on SQL Server Services and go to Properties.ģ.

Open SQL Server Configuration Manager (Start –> All Programs –> Microsoft SQL Server 2008 –> Configuration Tool). Once we restored the master database, the above logins will not exists on the SQL Server instance.Ģ. 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. Make sure you have a valid master database backup. Also make sure that you have a valid master database backup available from which you are going to perform the restoration. Restoration of the master database can not be performed while SQL Server service running in default mode. Please don’t restore the master database on a production environment unless its required.īefore we restore the master database, the SQL Server service must be restarted in Single user mode. Note: If you want to practice the restoration of the master database, I suggest to use a test machine. As I was creating a test server on SQL Server 2008, I thought of writing this post for future reference.

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). 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. I personally believe that a Database Administrator spend most of his time with Backup & Restore.

Sql server restore master