How To Fix SQL Database Restore Failed, Database Is in Use

When restoring the SQL Server database from backup, you may encounter the “Restore of database failed, Database is in use” error message. This happens if someone is running queries or if you have an active connection or active windows running on the database to be restored.

The complete error message looks like the following:

Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.

The above message means that you cannot restore the database when it is being used.

Now, let’s see how to fix the ‘database is in use’ error in SQL Server.

How To Fix SQL Database Restore Failed, Database Is in Use Error

Below, we will mention different methods to solve this error.

Method 1: Close the Connections

As mentioned above, this error can occur if there are any active connections to the database. So, you can close any existing connections to the database to fix the issue. Here are the steps:

• Open SQL Server Management Studio (SSMS). 
• In Object Explorer, right-click the Databases option and then click Restore Database.
• In the Restore Database window, go to the Options page and check the Close existing connections to destination database option.
• Open SQL Server Management Studio (SSMS). 
• In Object Explorer, right-click the Databases option and then click Restore Database.
• In the Restore Database window, go to the Options page and check the Close existing connections to destination database option.

This will close all the existing connections.

Alternatively, you can use the following T-SQL commands to close the existing connections.

USE [master]

ALTER DATABASE [stellardb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

BACKUP LOG [stellardb] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\stellardb_LogBackup_2023-09-17_08-28-49.bak' WITH NOFORMAT, NOINIT,  NAME = N'stellardb_LogBackup_2023-09-17_08-28-49', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5

RESTORE DATABASE [stellardb] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\stellardb.bak' WITH   FILE = 1,   NOUNLOAD,  STATS = 5

ALTER DATABASE [stellardb] SET MULTI_USER

 GO

Once all the SQL server connections are closed, you can proceed with the restore operation.

Method 2: Set the Database to Single-User Mode

You can also set the database to single-user mode to close the connections. In this, you need to:

1. Set the database to single-user mode and disconnect the other users.
2. Then, set it again to multi-user mode if necessary.

You can use the following code in T-SQL to set the database to single-user mode:

ALTER DATABASE [stellardb]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

This will disconnect all the existing connections except one.

To set the database again to multi-user mode, you can use the below code:

ALTER DATABASE [stellardb] SET MULTI_USER

Method 3: Bring the Database Offline

To resolve the issue, you can also take the database offline. This will disconnect the database and remove all the existing connections. To do so, follow the below instructions:

• Open SSMS and go to Object Explorer. 
• Under the Databases option, right-click the database you want to take offline.
• Then, select Tasks > Take Offline.

Once the database is offline, it will remove all the existing connections.

After that, you can bring it online. To do so, right-click the database and select Tasks > Bring Online.

Method 4: Restart the SQL Service

You can also restart the SQL service to disconnect the users and close all the connections in all the databases. The entire SQL Server will be restarted.

To do this, in SSMS, right-click your SQL Server in the Object Explorer and select the restart option.

Note: Use this method only if the previous methods fail. 

What To Do if the Issue Still Persists

If the issue is still not resolved, it means that the database or backup file is corrupted. In such a case, you can use a third-party SQL repair software to fix the backup file and restore the database. The Technician edition of the software can repair the corrupt database files and also the backups. It can restore all the database objects from the backup and save them in a New Database, Live Database, or Other Formats (like CSV, HTML, and XLS). The software supports all SQL Server backup types created in any SQL Server version.   

You need to install Stellar Repair for MS SQL Technician and select the Extract from MS SQL Backup option. Then, follow some simple steps to fix your SQL Server backup file.

Conclusion 

The error “Database is in use” appears when restoring the SQL Server database from the backup. As indicated in the message, if the database is in use, you cannot restore it. To fix this error, you can follow the solutions mentioned in this article. You need to check and close existing connections before trying to restore the database. If the manual solutions fail, then there might be a chance that the backup file is corrupted.