Welcome to deBUG.to Community where you can ask questions and receive answers from Microsoft MVPs and other experts in our community.
0 like 0 dislike
2.1k views
in SQL Server by 2 3 5

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

Exclusive access could not be obtained because the database is in use.


1 Answer

1 like 0 dislike
by 158 184 366
selected by
 
Best answer

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".
    restore database in use
  3. In general, select the source of your backup.
  4. In options, check "Close existing connections to destination database".
    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

If you don’t ask, the answer is always NO!
...