SQL Database Restore Failed Database Is in Use
Error Details - SQL Database Is in Use
"I'm using SQL Server 2008 R2, I need to restore a database from a .bak file. There is always an error that the database is in use until I restart the SQL Server service."
This is a comment on StackOverflow. As you see, when we try to restore a database in Microsoft SQL Server Management Studio (SSMS), it does not work because some other users are currently using the database, too. Usually, the full error message is like this.
Restored failed for Server
Additional Information: System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use.
Solutions - How to Fix Database in Use Problem
Applies to: MS SQL Server 2000, 20005, 2008, 2008 R2, 2012, 2014, 2016, 2017...
Any version of SQL Server, when this kind of problem occurs, can benefit from the provided solutions here. It's possible to restart the Server service as the StackOverflow user does, but do not take it as the first step regarding the data safety. Next, we advise users to go through the four procedures to rectify the problem.
1. Check active users
Since the error message hints that someone is using the database, you need to check who it is and how to notify him/her to stop it. Usually, administrators use the built-in systems sp_who & sp_who2 to find processes and users using the database.
This is not for releasing the occupied database but it's necessary to perform in advance! You don't wish to disconnect a user who is doing a very important task without a notification.
2. Restart the service
It's not recommended at the very first step, as we've explained, for data safety. On the other hand, if you didn't find any active user by running sp_who or sp_who2 at the moment, it's risk-free to restart the SQL server service.
There are five typical ways to restart the server service, including using the SSMS, Microsoft Services Console, Command Line, SQL Server Configuration Manager, and Powershell. Here, we'll show you how to restart SQL Server service in Configuration Manager.
Step 1: Open SQL Server Configuration Manager. In Windows Server 2008 or 2003, for example, you can click Start > Programs > Microsoft SQL Server > Configuration Tools > SQL Server Configuration Manager.
Step 2: In the left pane of the SQL Server Configuration Manager, click SQL Server Services. All services are in the right pane. Right-click on SQL Server service and you can then select Restart from the context menu.
3. Start SQL Server in Single-user Mode
The other way to disable all the connected users without notifying them is to alter the default multiple-user mode to single-user mode. Use this code to abort all existing connections before attempting to restore.
USE master;
GO
ALTER DATABASE YourDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE YourDB
SET MULTI_USER;
GO
4. Fix Corrupted Database or Backups
If it's true, trying to restart the service or disable connected users under single-user mode should be able to fix the error, and it won't show up again during the SQL server restore process. Unless it's the problem of database or backup itself.
Sometimes, for a similar situation that is hard to identify its cause after trying every possible means, it's a good idea to follow the software's way to recover, repair or rebuild the master database in SQL server.
Qiling SQL Server Recovery software is exclusively designed for restoring a corrupted SQL database to a normal state. It's economical and simple SQL database restore tool that works in most cases, such as SQL server shutdowns, corruption, backup failed, restore failed and even records were deleted. So, in the event that the former three solutions didn't help you get rid of the failure, try Qiling software this time. See if it works by repairing the corrupted database and backups.
Step 1. Close the SQL Service in Task Manager or SQL Server Manager Console.
Step 2. Run Qiling MS SQL Recovery and click "Next" to start.
Step 3. Choose the location where you saved your corrupted database. Afterward, Click "Scan" to start analyzing the specified database.
Step 4. When it has done, choose the files you want to repair and click "Recover".
Step 5. Turn on the SQL service, you can choose to export the repaired files to database to export it as SQL scripts.
Related Articles
- How to Recover Deleted SQL Server Database without Backup
- SQL to XML | How to Export Data from SQL Server to XML
- MS SQL Database Restore with Recovery and Norecovery Options
- Restore Database from MDF and LDF Files in SQL Server 2017/2014/2012