Error: SQL Server 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, or users not closing the SQLQuery window after finishing a query task. Anyway, you can try out the following proven ways to fix it.
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:
ALTER DATABASE databasename
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE databasename
Execute it to destroy all existing connections before restore.
Way 3. Restore SQL Server database with recovery
There’s another simple tip that works for me, 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. Alternative to restore SQL database without error
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, I prefer to manage SQL database backup and restore with error-free alternative like AOMEI Centralized Backupper Database.
It 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. I will provide a quick guide here, you can download the 30-day free trial to proceed:
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.
SQL Server restore failed because database is 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 and smoothly.
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.