Sql-server – Restoring master database

restoresql serversql-server-2012

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.

Best Answer

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.

Related Question