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

When trying to restore SQL Server database from backup, you can experience different errors. The “SQL database restore failed, database in use” is one such error that occurs when you try to execute queries that require exclusive access to the database but it is currently in use. The complete error message reads as follows:

Msg 3101, Level 16, State 1, Line 2

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

This error indicates that the database cannot be restored as it is in use. Let’s understand the reasons that can lead to this error in SQL Server and see how to resolve it.

Reasons behind ‘SQL Database Restore Failed, Database is 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.

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

Below are some possible reasons that can result in the ‘Restore of database failed because the database is in use’ error:

• Some users are connected to the database you are trying to restore.
• There are multiple windows of SQL Server Management Studio (SSMS) are open during the database restore.
• Some users are connected to the master database.
• The backup (.BAK) file you are trying to restore is corrupted.

How to Fix ‘Database Restore Failed, Database is in Use’ Error in SQL Server?

The error message itself indicates that the database is in use by some users or there are active connections to the database. So, you need to first check and disconnect all existing connections to the database. You can use the sp_who Transact-SQL command to check the users who are currently using the database. This command will also provide information about current sessions and processes in an instance of the SQL Server database engine. If the database is in use by some users, you need to disconnect them and then try to restore the database. To disconnect the active users, you can follow the below-given methods.

Method 1: Select “Close Existing Connections to Destination Database” Option when Restoring the Database via SSMS

When restoring the database using the SSMS, you can select the “Close existing connections to destination database” option to close the existing connections to the SQL database. To do so, follow these steps:

Step 1: Open SSMS and connect to the SQL Server instance.

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

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:

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

• Select the database you want to restore.
• In the left panel, click Options.

Step 4: On the Options page, check the checkbox labeled, ‘Close existing connections to destination database’.

Note: This will set the database to single-user mode and then again to multi-user mode once the restore process is complete.

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

Method 2 – Set the Database to Single-User Mode

You can set the database to single-user mode from multiple-user mode. This will disconnect all the connected users. To set the database to single-user mode, follow these steps:

Step 1: Open SSMS and connect to the SQL Server instance.

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

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

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

Method 3 – Take the Database Offline

You can also remove the existing connections by taking the SQL database offline. To do so, follow these steps:

Step 1: In SSMS, under Object Explorer, right-click on the database you need to take offline.

Step 2: Click Tasks and then select Task Offline.

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.

Method 4 – Restart the SQL Server Services

Restarting the SQL services can also disconnect all the connections to the database. You can restart the service by using the SQL Server Configuration Manager. Here are the steps:

Note: Restarting the SQL server services will kill connections to all the databases.

Step 1: Launch the SQL Server Configuration Manager. You can find it at the following locations:

SQL Server 2022 C:\Windows\SysWOW64\SQLServerManager16.msc

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.

Step 3: In the right pane, right-click the SQL Server service, and Stop and Start it.

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

What if the problem still persists?

If the issue still persists, it means there is a problem with the backup file. There are chances that there is corruption in the database backup file. In such a case, you need to take the help of a third-party SQL database repair tool, such as Stellar Repair for MS SQL Technician. It is MVPs recommended tool to repair corrupt database files and backup files. It can restore all the data from corrupt SQL database backup (.BAK) files and save them in a new database with complete precision. The tool works with SQL Server 2022, 2019, and earlier versions.

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

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

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

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

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

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

Step 5: You will see two options – Standard Scan and Advanced Scan to scan the file. Select the Standard Scan mode. If the database file is highly corrupted, then click on Advanced Scan. Click OK.

Step 6: In Select Backup Set, select the backup set, and then click Next to proceed with the scanning process.

Step 7: Once scanning is complete, a Repair Complete dialog box will appear. Click OK. The software shows a preview of the repaired file.

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

Step 9: In the window that pops up, choose one of the below Saving Option:

• New Database
• Live Database
• Other formats

Step 10: Click Next.

Step 11: Next, specify the required details in the Connect To Server section. Also, select a location to save the repaired file (if you’re saving it as a new database) and then click Next.

Step 12: In Save Database, select the Save Mode, and click Save.

To know how this tool works, watch this video:

Conclusion

The SQL database restore failed, database is in use error can prevent you from restoring the SQL database. To fix this error, follow the methods explained above. If this error has occurred due to corruption in the SQL database backup file, you can use Stellar Repair for MS SQL Technician. It can help resolve the issue by recovering data from corrupt SQL Server database backup file and saving it in a new database file.

FAQ

1. What precautions should I take before restoring a database?

You need to check and close existing connections to the database before trying to restore the database. Also, verify the backup file integrity using the RESTORE VERIFYONLY command.

2. How can I identify active connections before restoring?

You can use the sp_who command to identify active connections to the SQL database.

3. Can a corrupted backup file cause the SQL database restore failed, database is in use  error?

Yes, you may encounter the “SQL database restore failed, database is in use” error if you try to restore a corrupt backup file.

4. What permissions are required to restore a database in SQL Server?

To restore a SQL Server database from backup, the user must have the Sysadmin Role, Dbcreate role, CREATE TABLE permissions, and ALTER DATABASE permissions.

Was this article helpful?

YES0

NO

About The Author

Priyanka Chauhan

Priyanka is a technology expert working for key technology domains that revolve around Data Recovery and related software's. She got expertise on related subjects like SQL Database, Access Database, QuickBooks, and Microsoft Excel. Loves to write on different technology and data recovery subjects on regular basis. Technology freak who always found exploring neo-tech subjects, when not writing, research is something that keeps her going in life.