Restore Master Database In Single User Mode

Restoring database requires exclusive access to the database. If the database already exists in the instance, the database needs to be set to single user mode to prevent other users connects to it. If there are users connected to it and you need to restore the database immediately, you can set database to single mode with immediate rollback option to roll back all incomplete transaction and disconnect all other user connection.

USE master;
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

This T-SQL command does not work for master database. The SQL instance is required to be started in single user mode in order to restore the master database from the backup.

There are a few ways of starting SQL Server instance in single user mode.

Configure server startup option
- Open up SQL Server Configuration Manager (SSCM)
- Please make sure the SQL Server agent service is stop before starting the SQL Server in single user mode
- In SQL Server Services, right click on the corresponding SQL Server database engine service you want to start in single user mode and select Properties
- In the properties window, select the Advanced tab, scroll down until you see Startup Parameters
- Insert –m; (don’t forget the semicolon and don’t leave any space) in front of the existing startup option. (You should have something like this,

-m;-d<drive>:\<Server>.<Instance>\MSSQL\DATA\master.mdf;
-e<drive>:\ <Server>.<Instance>\MSSQL\Log\ERRORLOG;
-l<drive>:\ <Server>.<Instance>\MSSQL\DATA\mastlog.ldf

- Click Apply and OK
- Restart the SQL Server database engine service in SSCM

After you are done, remove the -m; you added at the startup option and restart the service again for multi user mode.

Start database engine from command prompt using sqlservr.exe
- By default, the sqlservr.exe is located at C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn. If you have a named instance, it would be another copy of sqlservr.exe located in its corresponding binn folder. Please be aware that starting the sqlservr.exe from different folder will start up your instance with the different service pack. Hence it is important to use the appropriate sqlserver.exe that corresponds to your instance
- Start an elevated command prompt (run as administrator), move to the correct directory for example,
cd \Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn
- To start default instance in , enter command sqlservr.exe -m -c
- For named instance, enter command sqlservr.exe -m -c -s <InstanceName>
- If you just want to start SQL Server only recovering master database, enter command sqlservr.exe -m -c -s <InstanceName> -T3608

-f	Start SQL Server in minimal configuration and put server in single mode
-m	Start SQL Server service in single user mode
-c	Shorten startup time by skipping service not started in command prompt
-s	Start Server with named instance
-T	Start Server with specified trace flag.

More option can be found here.

Once the server is started, unless the -m”Client Application Name” is specified e.g. -m“sqlcmd”, you can use either SQLCMD or SSMS query editor to restore master database with Transact-SQL statement,

Using sqlcmd ultility,
- Open a command prompt
- Enter command,
Using windows authentication,
For default instance, sqlcmd -S <ComputerName>
For named instance, sqlcmd -S <ComputerName>\<InstanceName>

Using SQL Server authentication,
To type login with password visible, sqlcmd -U <Login> -P <Password> -S <ComputerName>\<InstanceName>
To prompt password, sqlcmd -U <Login> -S <ComputerName>\<InstanceName>

-S	[protocol:]server[\instance_name][,port]
-U	login_id
-P	password

More option can be found here.

- Input restore Transact-SQL statement and enter,

RESTORE DATABASE master FROM <backup_device> WITH REPLACE;

e.g.

RESTORE DATABASE master FROM 
DISK = N‘C:\Program Files\Microsoft SQL\MSSQL10_50.MSSQLSERVER\MSSQL\BACKUP\Master_Backup.bak’ 
WITH REPLACE;

- Input GO and hit enter.

After master database is restored, exit out sqlcmd window. Remove the single user startup parameter –m; (if previously added in SSCM) and restart the SQL Server service.

Using SSMS query editor
- Open SSMS, the object explorer may not work since it requires multiple . Execute the SQL restore Transact-SQL statement mentioned above through query editor.
- After master database is restored, close SSMS. Remove the single user startup parameter –m; (if previously added in SSCM) and restart the SQL Server service.