Restore of database ‘SID’ failed. Exclusive access could not be obtained because the database is in use.



Share this article :

Symptom

You are getting below error while trying to restore a database using SQL Server Management Studio.

SQLServer - Restore of database failed

Restore of database ‘SID’ failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

Additional information:
System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.SmoExtended)

Solution

1. Login to your Microsoft SQL Server Management Studio. Right click to your database and select Properties. Next, select Options page. On your right panel, scroll down and till you see Restrict Access. Now, change the state to SINGLE_USER.

SQL Properties - Single User Restrict Access

2.  Now, you will see the single user symbol on the database. You may proceed to restore the database again.

SQL - Single User Mode

3. Alternatively, you may execute below command to change to SINGLE USER mode.

USE master
Go
ALTER DATABASE DATABASE_SID SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

4. Don’t forget to set it to MULTI USER access once restoration is done.

ALTER DATABASE DATABASE_SID SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Related posts

Stop & Start MSSQL Server 2005
Add & Remove SQL Server tempdb
MSSQL Server Management Studio: Value cannot be null
How to Activate xp_cmdshell Server Configuration Option
© 2017 ITsiti. All Rights Reserved
Powered by KEEM