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 a simpler alternative to manage SQL database backup and restore smoothly. Here I would like to use AOMEI Centralized Backupper for Database as an example.

Its SQL Server backup feature supports SQL 2005-2019, and enables you to select multiple databases to backup or restore at once. As a centralized management solution, it works for not only your own PC, but all the computers within 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 supports other data types in Windows such as files, partitions, disk an OS.

You can download the 30-day free trial to proceed and get a up-to 60% off discount:

Create a SQL Server database backup task:

1. Install the software and client packages properly, obtain the controls over the computers you want to manage.

Then you can go to Tasks tab, choose New Task and select SQL Server Backup.

2. In Step 1, click + Add Computers to detect the client computers with SQL database, and select the client that you want to backup.

3. In Step 2. Click + Add to detect all the instances on the selected computer, and select the database(s) according to your needs.

4. In Step 3. Click Add Storage to add a share or NAS device as the storage end. Then Start Backup.

Settings: You can enable email notification here.
Schedule: Create a schedule to run the backup Once/Daily/Weekly/Monthly. Full backup and differential backup options are also included in Advanced tab.

Restore SQL Server databases from backup:

1. The created backup task will be displayed in Tasks tab. You can click Advanced at its top-right corner to Restore from the backup.

2. Select the computer and its databases you want to restore from. If you’ve backed them up several times (full or differential), you can also select the specific one to restore in the upper right side.

3. You can restore these databases to original location or new location. With the second option, you can directly restore databases to another SQL Server. Then, click Start Restore to execute it.

Overwrite the existing database (s): With this option ticked, the backed up databases will overwrite the existing databases of the same names. Otherwise, the databases with the same names will be skipped during restoring.

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 Centralized Backupper Database Edition, which can complete the database backup and recovery operation intuitively.

As a comprehensive backup solution, it works not only for SQL Server databases, but also for computer data like files, partitions, OS and disks. With it, you can even perform centralized system backup for all computers within LAN.