-
Title
Error: Exclusive access could not be obtained because the database is in use. when restoring msd -
Description
Error when running exec master.dbo.xp_restore_database @database = N'msdb' via script in Query Analyzer
Server: Msg 62309, Level 19, State 1, Line 0
SQL Server has returned a failure message to LiteSpeed 2005 which has prevented the operation from succeeding.
The following message is not a LiteSpeed 2005 message. Please refer to SQL Server books online or Microsoft technical support for a solution:
RESTORE DATABASE is terminating abnormally.
Exclusive access could not be obtained because the database is in use when restoring msdb -
Cause
Exclusive access of the database could not be obtained.
-
Resolution
To start SQL Server in single user mode:
Start a command session (start > Run > "cmd" [Return]).
To ensure that SQL Server is not running type:
net stop MSSQLServer
This may also stop other services (typically SQLSERVERAGENT). It will also indicate if the service is already stopped.
To start SQL Server in single user mode type:
sqlservr -m
If this is not recognised then cd to 'C:\Program Files\Microsoft SQL Server\MSSQL\BINN' and try again.
Note:
Single User mode will allow only a single user to connect to the database.
While SQL Server is in single user mode you should still be able to connect from SQL Server Query Analyzer if you connect using the "sa" account.
You can set a single database into single user mode using an alternative procedure described here: How to set a single database into Single User Mode - but this process cannot be used to set the MASTER database into single user mode, for that you must start SQL Server in single user mode.
To shutdown the server when it is in single user mode, simply type Control-C in the command window where sqlservr is running. It will then ask you if you wish to shutdown SQL Server.
You can then restart SQL Server by restarting the SQL Server process:
net start MSSQLServer
Be aware that this will not start any other processes which may have been stopped, such as SQLSERVERAGENT. So if any other processes where stopped then you will need to manually restart them (or reboot if that is easier - assuming the necessary services are set to start automatically).