stopping the MSSQLSERVER service (logged as

[leeym]

We backed up our SQL Server 2005 DB (master.bak, msdb.bak, etc.) and now we have problems restoring it. Apparently we have to restore the master DB in single-user mode, but we are having major issues. This is what we did:

1) Logged on as a local admin
2) Stopped the MSSQLSERVER & SQL AGENT services (logged as the local system account). We stopped all other SQL-related services for good measure
3) At cmd prompt (C:\program files\microsoft sql server\mssql.1\mssql\binn) we typed 'sqlservr.exe -m'

Here are the errors:

ERROR 18461: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Server is in single user mode. Only one administrator can connect at this time.

Then it repeats itself and generates this message:

ERROR 18461: Login failed for user 'DOMAIN\ADMINISTRATOR'. Reason: Server is in single user mode. Only one administrator can connect at this time.

How can we get around this? Are there other services we must turn off? What am I missing? Also, I think I have to use the RESTORE DATABASE command vs. the SQL Server Mgmt Stdio GUI, correct?

Thanks

 

[mrdenny]

In order to restore the master database you can't use the GUI. The GUI requires that you be able to log in a couple of times in order to work.

Log into the machine as a member of the Administraors group and the sysadmin fixed server role. Then run this command from a command prompt:

sqlserver.exe -m -c

Leave the window open, and open another command window. In that window run:

sqlcmd -S localhost -E

That will log you into the SQL Server.

You can now restore master using the RESTORE DATABASE command.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[leeym]

we realized that another service started was logged in as the same user. we shut it down and were able to start the sql service in single-user mode. thanks