How to put SQL Server 2012 in single-user mode

Question

I am trying to restore master DB from a backup and getting below error:

To restore the master database, the server must be running in single-user mode. For information on starting in single-user mode, see "How to: Start an Instance of SQL Server (sqlservr.exe)" in Books Online.

I thought it would be simple however I have tried three different approach to meet the requirement but none of them worked.

First I used SSMS, right click on master DB > Tasks > Restore > Database. And check Close existing connections to destination database after selecting the backup. It returns me with below:

System.Data.SqlClient.SqlError: Option 'SINGLE_USER' cannot be set in database 'master'. (Microsoft.SqlServer.SmoExtended)

So I searched a bit and found an instruction from Microsoft

But after I added the startup parameter with -m and restarted the MSSQL Service. I still got the first error message while I was trying to restore master by

RESTORE DATABASE master FROM DISK='C:\...\master.bak' WITH REPLACE;

My last attempt was to launch SQL Server service by

net start MSSQLSERVER /m

Then connect it by sqlcmd and send my restore by it, like

sqlcmd -S localhost

And execute the restore cmd in dos command window.

Unfortunately, this also threw the first error message. It seems all operations above didn't really turn the SQL Server in single user mode for me.

Don't know which part I am doing wrong or what should I do next.

Answer 1

To restore master database SQL Server must be brought in single user mode, not the master database. As a fact you cannot bring master database in single user mode you would get error what you have pasted in the question.

To simply start SQL Server in single user mode. First stop SQL Server agent and then open command prompt with admin privileges. Run:

net stop MSSQLSERVER

MSSQLSERVER is default instance. If you have named instance it would be.

net stop mssql$instance_name.

After above is done check SQL Server services are stopped.

Now open new command prompt(as administrator) go to Binn folder and run

sqlservr -m--if you have default instance

sqlservr -m -s instance_name--for named instance

NOTE: It is sqlservr

This will cause SQL Server to start in single user mode. You can check more details Here.

Now go ahead and launch new Command prompt as admin and login as below

SQLCMD –S hostanme\instance_name - E--for named instance

sqlcmd -S -E--for default instance

-E parameter is here for trusted connection and -S is for server name

After you are able to connect, run the restore command.

Now Again stop SQL Server service and start, you can do this from SQL Server configuration manager.

Please read This Blog for your reference