Before we start, here’s some important information about the master database:

The master database records all the system-level information for a SQL Server system. This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings. In SQL Server, system objects are no longer stored in the master database; instead, they are stored in the Resource database.

Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the master database is unavailable.

Set MS SQL Server to Single User Mode

To set MS SQL Server to single user mode, do the following:

Launch SQL Server Configuration Manager from the Programs menu

Click SQL Server Services

Right click SQL Server (<instance-name>) and select Properties

Click the Advanced tab and scroll to see Startup Parameters. You will see something similar to this:-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf

Append -m; to the above entry. That is you should now have something like this:-m;-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf

Restart SQL Server (<instance-name>)

Restore master database via SQLCMD

To restore the master database, do the following:

Open a Command Prompt as an administrator from the Programsmenu

At the prompt, type SQLCMDand press Enter

Then typeRESTORE DATABASE master FROM DISK = ‘backup-path\master.bak’ WITH REPLACE;

Prese enter and type: GO

Reset MS SQL Server to Multi User Mode

After you have successfully restore the master database, use these steps to set MS SQL Server back to multi user mode:

Return to the SQL Server Configuration Manager dialogue screen that you had open previously

Click SQL Server Services

Right click SQL Server (<instance-name>)and select Properties

Click the Advancedtab and scroll to see Startup Parameters. You will see something similar to this:-m;-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf

Remove -m; from the above entry. That is, you should now have something like this now: -dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf