"SQL database restore failed, the database is currently in use."

Error Details - SQL database is in use

"I'm using SQL Server 2008 R2 and I need to restore a database from a .bak file. However, I always get an error stating that the database is in use, and I have to restart the SQL Server service to proceed. How can I resolve this issue without restarting the service?"

This is a comment regarding StackOverflow. As you can see, when we attempt to restore a database in Microsoft SQL Server Management Studio (SSMS), it fails due to other users currently using the database. Typically, the full error message would look something like this:

Server recovery failed. Additional information: System.Data.SqlClient.SqlError: Cannot obtain exclusive access to the database because it is in use.

Solution - How to Resolve the Issue of a Database Being in Use

Applies to: MS SQL Server 2000, 2005, 2008, 2008 R2, 2012, 2014, 2016, 2017...

Any version of SQL Server can benefit from the solution offered here. You could restart the Server service, as one Stack Overflow user suggested, but don't make that your first line of defense for safeguarding your data. Instead, we recommend a four-step approach.

1. Check active users

Since the error message indicates that someone else is using the database, you need to find out who it is and how to ask them to stop. Typically, an administrator would use the built-in system stored procedures sp_who and sp_who2 to look for processes and users accessing the database.

This is not to free up the database, but rather something that needs to be done in advance! You certainly don't want to disconnect users in the middle of an important task without warning.

2. Restart the service

As we mentioned earlier, this is not recommended initially, for reasons of data safety. On the other hand, if you run sp_who or sp_who2 and don't see any active users, it is safe to restart the SQL Server service.

There are five common methods to restart the SQL Server service, including using SSMS, the Microsoft Services Console, the command prompt, SQL Server Configuration Manager, and PowerShell. Here, we'll show you how to restart the SQL Server service in the Configuration Manager.

Step 1: Open the SQL Server Configuration Manager. In Windows Server 2008 or 2003, you can do this by clicking Start > Programs > Microsoft SQL Server > Configuration Tools > SQL Server Configuration Manager.

Step 2: In the left pane of SQL Server Configuration Manager, click SQL Server Services. All the services appear in the right pane. Right-click the SQL Server service, and then select Restart from the context menu.

3. Start SQL Server in single-user mode

Another way to disable all connected users without notice is to change the default multi-user mode to single-user mode. Use the following command to terminate all existing connections before attempting a recovery:

Use the master database; Continue; Switch to the YourDB database; Set single-user mode; Roll back any active transactions immediately; Switch to the YourDB database; Set multi-user mode; Continue.

4. Repair a corrupted database or restore from backup

If that's the case, attempting to restart the service or disable the connected user in single-user mode should fix the error, and it shouldn't reappear during SQL Server restore, unless there's an issue with the database or the backup itself.

In some similar cases, if all else fails and the cause is still hard to determine, it's best to recover, repair, or rebuild the master database in SQL Server the way the software intended.

The SQL Server Recovery tool is specifically designed to restore corrupted SQL databases to a working state. It's an affordable and straightforward SQL database recovery solution for most scenarios, including SQL Server crashes, corruption, backup failure, restore failure, or even deleted records. So, if the above three solutions don't help, give this software a try. Use it to repair the corrupted database and backups to see if they yield any positive results.

Step 1: Close the SQL service from the Task Manager or the SQL Server Management Console.

Step 2: Run the SQL recovery tool and click on “Browse” (two dots) or “Search” to select the location where the corrupted database is saved. Thereafter, click on “Repair” to start analyzing the specified database.

Step 3. Once the scan is finished, select the files you want to fix, and click “Export.”

Step 4: Start SQL Service, you can choose to export the repaired files to a database or as a SQL script.