[Error Solved] SQL Database Restore Failed, Database in Use

When trying to restore SQL Server database from backup, it is common to receive an error message that reads as follows:

Restored failed for Server ‘xxx’ (Microsoft.SqlServer.SmoExtended)

Additional Information: System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use.

Reasons behind ‘SQL Database Restore Failed, Database in Use’ Error

Below are some reasons that could interfere with the restore process and throw the ‘restore of database failed because the database is in use’ error:

• You are connected to the database you are trying to restore.
• While using SQL Server Management Studio (SSMS) to do a database restore, you have more than one window open in it.
• Other users are connected to the master db.

Now, we will discuss solutions to fix the error.

Tip: SQL Server database can be restored from the backup (.bak) file. But, the database restore operation may fail if the .bak file is corrupt. Use Stellar Toolkit for MS SQL software that comes with an efficient SQL backup extractor tool designed to help database administrators recover SQL database from corrupted backup (.BAK) file. The software supports SQL Server 2019, 2017, 2016, 2014, 2012, & older versions.

Before We Begin

Before proceeding with resolving the error – exclusive access could not be obtained because the database is in use, make sure to meet the following prerequisites:

• SQL Server, of any version, must be installed on your system.
• You will need SQL Server Management Studio (SSMS) installed on your computer.

How to Fix ‘can’t restore SQL database because it is in use’ Problem?

When attempting to restore SQL Server db, make sure there are no active connections. If someone is using the database, the restore operation will fail. To resolve the issue, you will need to disconnect the active users. You can do so, by following any of these methods:

NOTE: Before disconnecting the users, use SQL stored procedure ‘sp_who’ to check all users currently using the db. If you find users performing some important tasks, notify those users before disconnecting them. For detailed information on sp_who, refer to this link. If you don’t want to notify users, skip to method 2.

Method 1 – Close the existing connections to the database

To close existing connections to SQL db, follow these steps:

Step 1: Open SSMS and connect to the db.

Step 2: After connecting to the database, Object Explorer panel will appear on the left side of the SSMS window.

Step 3: In Object Explorer panel, right-click Databases, and then select Restore Database.

Step 4: In Restore Database dialog box, do the following:

• Select one of the databases to restore.
• In the left panel, click Options.

Step 5: In Options page, check the checkbox labeled, ‘close existing connections to destination database’.

Once the SQL Server connections are closed, proceed with the restore operation.

Method 2 –Change from multiple-user mode to single-user mode

Changing the multiple-user mode by default to single user mode will disconnect all the connected users. This option can be used, if you want to disconnect all the users without notifying them.

To force users to go offline (i.e. disconnect) from SQL Server, set the db from multiple-user mode to single-user mode by following these steps:

Step 1: Open SSMS, connect to the database.

Step 2: In Object Explorer window, select New Query. Copy and paste the below T-SQL code snippet into the query window, and then click Execute:

USE master;
GO
ALTER DATABASE AdventureWorks2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

Executing the above code will change the database to single-user mode.

Method 3 – Restart the SQL Server Service

You can also disconnect the users by restarting the SQL service. You can restart the service by using SQL Server Configuration Manager, SSMS, services console, or the command line.

NOTE: Use this method as a last resort. That’s because, you may only need to restore a single db, but restarting the server will kill connections to all databases.

Steps to restart the service from SQL Server Configuration Manager are as follows:

Step 1: Browse SQL Server Configuration Manager using any of the following path:

SQL Server 2019              C:\Windows\SysWOW64\SQLServerManager15.msc

SQL Server 2017              C:\Windows\SysWOW64\SQLServerManager14.msc

SQL Server 2016              C:\Windows\SysWOW64\SQLServerManager13.msc

SQL Server 2014              C:\Windows\SysWOW64\SQLServerManager12.msc

SQL Server 2012              C:\Windows\SysWOW64\SQLServerManager11.msc

Step 2: In the left pane of SQL Server Configuration Manager window, click SQL Server Services. And in the right pane, right-click SQL Server service, and Stop and Start it.

Step 3: Click OK to exit the SQL Server Configuration Manager.

What if the problem still persists?

If the issue still persists, likely there is a problem with your database or the backup file, used for restoring the database, is corrupt. In that case, use Stellar Toolkit for MS SQL. The software can recover db from a corrupt SQL Server. It can also extract a database – from corrupt backup (.bak) files – that need to be restored.

Stellar Toolkit for MS SQL software can also help you reset lost or forgotten SQL Server Administrator and user passwords. You can read the software review done by MVP from here.

To restore database from corrupt SQL Server backup (.bak) file by using the software, follow these steps:

Step 1: Download, install and launch Stellar Toolkit for MS SQL software.

Step 2: In software’s user interface, select Extract from MS SQL Backup.

Step 3: In Stellar Backup Extractor for MS SQL window, click Select File to choose the .bak file.

NOTE: Choose ‘Search in Folder’ option, if you do not know the file location.

Step 4: After selecting the .bak file, click Scan.

Step 5: The BackupSet window appears with details of all the backups.

Step 6: Choose the .bak file you want to recover from the Backup Type list, and then click Next to proceed with the scanning process.

Step 7: Once scanning is complete, a dialog box appears displaying the number of total records available in the backup file.

Step 8: The software shows a preview of the database records.

Step 9: To save the recovered .bak file, click Save on File menu.

Step 10: In the window that pops-up, choose MSSQL under Save As, and then click Browse to select the location to save the recovered file. Click OK.

Step 11: Choose New Database or Live Database under Saving Options. Next, specify details required in Connect to Server section, and then click Connect.

Step 12: Click OK when the ‘Recovery process successfully completed’ message appears.

The recovered file will get saved in the selected location.

You can watch the complete video from here:

https://www.youtube.com/watch?v=cezAhyUdipU

Conclusion

This blog explained how to fix the SQL database restore failed, database in use problem. You can disconnect active users by closing the existing connections or by changing from multiple-user mode to single-user mode. Or, disconnect all the users by restarting the SQL Server service. But, if you still have issues restoring the db, Stellar SQL Database Toolkit can come in handy. It helps resolving the issue by repairing the corrupt SQL db or by recovering the SQL Server backup file.