1

I am trying to restore master database, when I set the server to single mode user I am unable to connect I get the error below.

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed for user 'sa'. Reason: Server is in single user mode. Only one administrator can connect at this time.

3

Start SQL Server with the -m parameter, specifying the application name allowed to take the only available connection. For instance, to allow sqlcmd to take the connection:

sqlservr -m"sqlcmd"

If you want to work with SQL Server Management Studio, use this:

sqlservr -m"Microsoft SQL Server Management Studio - Query"

Remember that the "Connect..." button in Management Studio connects the object explorer, that would take your connection without letting you open another one to run the query. In this case, open a query window either disconnected or connected to another server and then use the "Connection, Change Connection" command from the context menu.

See the docs for more information: http://msdn.microsoft.com/en-us/library/ms188236.aspx

An interesting thing to note is that the Configuration Manager doesn't let you enter -m"Microsoft SQL Server Management Studio - Query" as a parameter (I suppose it's the spaces in the parameter), so this is an option only when starting SQL Server from the command line. If you decide to do this, remember to start cmd as an Administrator.

2

I've got a couple of blog posts on this, the process is fairly simple:

  1. Using SQL Configuration manager, stop the SQL Server instance
  2. Open a command window
  3. In the command window, start the instance executable in maintenance mode
  4. In a second command window, open SQLCMD
  5. Restore master from within the SQLCMD window
  6. Using SQL configuration manager, restart instance

For screenshots you can read http://thomaslarock.com/2014/01/restore-the-master-database-in-sql-server-2012/

1

Make sure the SQL Server Agent service is turned off before switching the server in single user mode because when you start an instance of SQL Server in single-user mode and if you have not stopped SQL Server Agent service before hand SQL Server Agent might connect first and prevent you from connecting as a second user.

The same has been documented by Microsoft in This Article

  • I agree to what Colin mentioned but I am afraid Raadee IMO has provided a correct solution. Before putting database in single user mode you MUST disable SQL Server agent. He just needs to improve his answer and provide more details. Raadee can you add that – Shanky Nov 14 '14 at 18:31
0

Check for any external connections connecting on the same port # using netstat. Sometimes application services that automatically connect to the instance keep connecting even if you've declared a maintenance window like this. Once identified, disable them at the source, or temporarily block them by firewall rule, change in port # or disabling the external protocols in Configuration Manager (Named Pipes and TCP/IP).

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.