6 Ways to Fix SQL Server Restore Failed Database in Use

Error: SQL database restore failed because the database is in use

When you use T-SQL or SSMS to restore SQL databases, sometimes an error may occur saying “Exclusive access could not be obtained because the database is in use” and the problematic database will be followed by a "restoring..." mark, which blocks you from any normal operation on it.

What causes SQL database stuck in restoring issue? It could be that another user is using the database, or the database file is synchronized during the re-establishing process. Anyway, i've listed several proven ways here, you can try them one by one to fix the SQL database restore failed database in use error:

• Way 1. Close existing connections to destination database
• Way 2. Switch SQL Server to single user mode
• Way 3. Restore SQL Server database with recovery
• Way 4. Change the default database back to master
• Way 5. Restart the SQL Server service thoroughly
• Way 6. Use secure alternative to restore SQL database

🔹Note: Before trying these methods, please first check if there's any SQLQuery window you forget to close after finishing a query task. If so, close it and the sql server database restoring issue may be solved directly.

Way 1. Close existing connections to destination database

If you prefer to use SSMS GUI, try ticking one more option in Restore Database window to close all the existing connections to the target database.

1. Similar with normal operation, launch SSMS and connect to your instance, then right-click the database you want to restore and choose Tasks > Restore > Database.

2. In the popping-out window, choose Device and specify the bak file you want to restore from.

3. Switch to Options tab, and tick the Close existing connections to destination database option. Then click OK to perform the recovery. The database in use error shall not pop up again.

Way 2. Switch SQL Server to single user mode

Since the SQL Server restore may fail because the database is used by other users, you can switch SQL Server to single user mode, and then set it to multi user mode again. This will close all the active connections as well.

Launch SSMS and connect to the instance, then you can create a New Query with following commands:

USE master;
GO
ALTER DATABASE databasename
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE databasename
SET MULTI_USER;
GO

Execute it to destroy all existing connections before restore.

Way 3. Restore SQL Server database with recovery

There’s another simple tip to fix SQL database restore failed database in use, i.e. restore the problematic database WITH RECOVERY.

Click New Query and enter this command in the window:

RESTORE DATABASE databasename WITH RECOVERY

Execute the command. When you disconnect the instance and connect it again, you will find the “Restoring” mark behind the database has been removed.

Way 4. Change the default database back to master

The SQL Server database restoring issue is sometimes due to your login using the target database as its default database, which will always keep a connection session alive. To fix it, you need to change the default database back to “master”.

1. Connect to your instance in SSMS, navigate to Security > Logins in Object Explorer and find your login user.

2. Right-click it to choose Properties. In the popping out Login Properties window, you will find the Default database at the bottom. Expand its drop-down menu and select master.

3. Click OK to confirm it. Then you can try recovering your target database again.

Way 5. Restart the SQL Server service thoroughly

This one is more of a final resort. If all other methods failed, then you can try restarting SQL service thoroughly.

1. Press Win + R and enter “services.msc” in the text box. Click OK to run it.

2. Find the SQL service of the corresponding instance, like SQL Server (MSSQLSERVER).

3. Right-click the service to Stop it, and then Start it again.

Way 6. Use secure alternative to restore SQL database smoothly

The database in use error is not rare in SQL Server recovery. In my case, it often appears when I restore multiple databases using T-SQL commands, and it’s really troublesome to repair them one by one each time. Therefore, you could consider an easier alternative to manage SQL database backup and restore smoothly. Here I would like to use AOMEI Cyber Backup as an example.

Its SQL Server backup feature supports SQL 2005-2022, and enables you to select multiple databases to back up or restore at once. As a centralized management solution, it works for not only your own PC, but all the devices within the LAN. Thus, you can also backup SQL database and restore to another server.

With its concise interface, any operation can be done via several simple steps. And not only SQL Server, it also supports VMware backup and Hyper-V backup.

You can download the 30-day free trial to proceed and get a time-limited discount:

💡 Create a SQL Server database backup task:

1. Run the installed AOMEI Cyber Backup on the server machine, navigate to Source Device -> Microsoft SQL -> Add Microsoft SQL.

Choose Download proxy program (or Copy link) and install it on to the device with SQL Server installed. Then, click Already installed proxy and select the device.

Next, click -> Authentication to validate the database instance. You can choose Windows Authentication or SQL Authentication.

2. Click Backup Task -> Create New Task to launch the task creating page. Choose backup type as Microsoft SQL Backup.

3. Click on Device Name to specify the SQL instances and databases for backup. You can select one or multiple databases as you need.

4. Select Target to save backups. You can either specify a local path or network path.

5. Before Start Backup, you can also set up a Schedule to run the SQL database backup daily, weekly or monthly, and set the backup method as Full, Incremental or Differential Backup.

Execute the task. Once it's finished, you will find it in the Backup Task tab.

✍ More useful features:
* Backup Cleanup helps you to delete older backup version automatically and therefore save storage space.
* Email Notification enables you to receive email notifications when the task is abnormal or successful.

💡 Restore SQL Server database from backup:

1. Go to Backup Task page, find the task you want to restore database from, and click -> Restore.

2. Select the specific database backup version that you want to restore, and click OK.

3. Select the target location that you want to restore to. By default, it will Restore to original location.

If you want to Restore to new location, you need to select target and specify the name of the new database. You can also modify the storage location.

Overwrite the database with the same name: If you tick this option, the backed up databases will overwrite the target databases of the same names. If you leave it unchecked, the target databases with same names will be omitted during restoring.

4. After all the settings, click Start Restore to begin the restore progress, and wait patiently for it to complete. You can click on the button to see restore details or cancel it.

When the restore is complete, you can see the restored database in Microsoft SQL Server Management Studio.

✐ Notes:

• "Restore to original location" must confirm the original location exist. Or else, you can only select "Restore to new location".
• "Restore to original location" will overwrite or delete the original database data, if the original database has important data, it is recommended to choose "Restore to new location".

Conclusion

SQL Server restore failed because database in use? Here are several solutions might work for you, but they do not prevent similar problems from recurring. To save your precious time and effort, you could try AOMEI Cyber Backup, which can complete the database backup and recovery operation intuitively.