3

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.

|improve this question
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

|improve this answer
  • Note that SQL Server (running in the command window) will automatically stop when the master database is restored. – Dan Guzman Mar 12 at 10:50
  • I tried the approach but encounter with The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/<MyPcFullName>:<MyInstanceName> ] for the SQL Server service. Windows return code: 0x2098, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered. – 蕭為元 Mar 15 at 2:49
  • When I tried to launch SQL by sqlservr -m -s instance_name – 蕭為元 Mar 15 at 2:51
  • Let me perform the steps on my PC and will let you know in sometime – Shanky Mar 15 at 6:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.