How to fix SQL Database Restore Failed: ‘Database in Use’ Error

MS SQL database files are susceptible to corruption and consistency issues. If your database is corrupted or damaged, you can restore the database from backup.However, sometimes, while restoring the backup file, you may encounter an error – SQL Database Restore failed: Database in use. This error usually occurs when there are active connections to the database or issues with your backup file. In this post, we will discuss some workarounds and troubleshooting methods to resolve the SQL database restore failed error.

Solutions to Resolve SQL Database Restore Failed: Database in Use Error

First of all, restart the SQL Server services and see if it helps resolve the issue. If it doesn’t, follow the workarounds and troubleshooting methods mentioned below to resolve the SQL Database Restore Failed: Database in Use error.

1. Close Existing Connections to the Database

The error may occur when the database you are trying to restore is in use by some other users or there are active connections to the database. To resolve this, you can check and disconnect all existing connections to the database.

To check the users who are currently using the database, you can use the sp_who Transact-SQL command. If the database is in use by some users, then you can select the “Close existing connections to destination database” option when restoring the database via SQL Server Management Studio (SSMS). Follow the below steps to do so:

Note: Before proceeding, ensure that you have the ALTER permissions to the database.

• In SSMS, connect to the SQL Server instance.
• Open the Object Explorer panel, right-click Databases, and then click Restore Database.
• In the Restore Database dialog box, select the database you want to restore. In the left panel, click Options.
• On the Options window, check the checkbox ‘Close existing connections to destination database’.

This will close all existing connections to the database. After this, proceed with the restore operation. Once the restore process is complete, then set the database to multi-user mode.

Alternatively, you can follow the below steps to set the database to single-user mode to disconnect all the connected users:

• Open SSMS, connect to an instance of SQL Server database engine, and expand it.
• Right-click on the database and then click the Properties option.
• In the Database Properties window, click the Options page.
• Under Options page, in Restrict Access, click SINGLE_USER and then click OK.

2. Check and Provide Desired Permissions

The “Restore failed database” error can occur when you do not have permission to access the folder in which the backup file is located. So, make sure you have the read, write, and create permissions on the folder where the .bak file is stored. To check and provide permissions, follow these steps:

• Go to Program Files and search for the folder where your backup file is saved.
• Right-click on the folder and select Properties. Check the permissions. If you do not have permissions on the folder, click the Security tab and select Advanced.
• In the Advanced Settings window, click on Change Permissions.
• Click Administrators > Edit.
• Select the Full Control box, below the Allow column.
• Click OK > Apply.

3. Repair the Backup File

If the backup file is corrupt, you can encounter various errors and issues, including the SQL database restore failed error. If your backup file is corrupted or damaged, there is no native tool in MS SQL Server to repair the corrupt backup database file. So, the best option is to use a professional SQL repair tool, such as Stellar Repair for MS SQL Technician. This tool can easily repair corrupt SQL database backup (.bak) files and NDF/MDF files.

It can restore all the data, including the deleted objects from damaged backup files, and save them in a new database file with 100% integrity. It can help recover data from backup files of all types, including Full, Differential, or Transactional. The tool supports SQL Server 2022, 2019, and earlier versions.

Conclusion

The SQL Database Restore Failed: ‘Database in Use’ error can occur due to numerous reasons. You can follow the above-mentioned workarounds and troubleshooting methods to resolve the error. You can close the existing connections to the database before trying to restore the database and ensure that you have all the required permissions on the folder where the backup file is stored.

If the error is the result of corruption in database backup file, then you can use the Stellar Repair for MS SQL Technician to repair the .bak file with complete precision. This professional tool can easily repair severely corrupted SQL database and backup files and retrieve all the data with 100% integrity. It can help resolve corruption-related errors in SQL Server.