Restore failed for server, the database in use

Question

Yesterday, I performed a database backup and when I tried to restore the taken database backup I got this error Restore failed for Server: Exclusive access could not be obtained because the database is in use.

TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'sql instance'.  (Microsoft.SqlServer.SmoExtended)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Exclusive access could not be obtained because the database is in use.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3101)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.1745&EvtSrc=MSSQLServer&EvtID=3101&LinkId=20476

Answer 1

The solution for "Restore failed for Server: Exclusive access could not be obtained because the database is in use." is closing all existing connection to the destination database by setting the database to a SINGLE_USER

How to restore the database is in use?

1. Open SQL Server Management Studio.
2. Right-click on databases > select "Restore Database".
   
3. In general, select the source of your backup.
4. In options, check "Close existing connections to destination database".
   

Alternatively, You can also restore your database using the below SQL query:

ALTER DATABASE DB_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE DB_NAMEFROM DISK = 'C:\Backuppath.BAK' 
GO

Note: whatever the way that you will use to restore the database in use, after performing the restore, you should make sure that the database is not set to SINGLE_USER.
If your database still in a SINGLE_USER state, you should set it to MULTI_USER by running the below query

ALTER DATABASE DB_NAME
SET MULTI_USER
GO

---

See Also

• Rename SQL Server database: The database could not be exclusively locked