Sql server restore failed because database is in use

In the blog, we’ll discuss the issue when the SQL restore failed because the database is in use. Due to this issue, a generic error in SQL server arises. So, we’ll be discussing that particular error. Also, the different approaches to resolve this issue will be explained.

Error – SQL Database Restore Failed, Database in Use

The error message is displayed as:

Restored failed for Server
Additional Information: System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use.

While restoring a database, you need to disconnect all the users using the option to close connections when you restore a database.

Use sp_who to check the active users on the database

Sp_who and sp_who2 are the two most used system stored procedures in SQL server. If you don’t want to disconnect the users immediately, you can check the active users by sp_who. Sometimes, you want to disconnect the users but don’t do that as they might doing some important tasks. So, it is good to first check the active users and notify them before disconnecting.

 Also Read: How to Repair SQL Server Database Corruption Issue

Don’t restart the device

You can restart your device using the SQL server management studio (SSMS) or the command line method. So, by restarting the SQL server, you can disconnect all the users connected to the server. But, use it only as the last option when all other methods do not work.

Switch to single-user mode from the multiple-user mode

Using the single-user mode by switching from multiple-user mode can disconnect all the users connected to the server. However, this approach should be followed only when you want to disconnect the users without notifying them.
For switching from multiple-user mode to single-user mode in T-SQL, you can use the following script:

USE master;
GO
ALTER DATABASE AdventureWorks2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

 Also Read: How to Attach MDF File into SQL Server Database

When there is corrupted backup

In some cases, it is not possible to create a backup because of the corrupted database. In such a scenario, you can adopt a professional tool for SQL Data Recovery. It is one of the most sought-after tools that recovers and restores the corrupted database files.

The tool also facilitates the users by recovering and restoring MDF as well as NDF files without hampering the original data. So, if you are facing difficulty in creating a backup because of the corrupted database, you should definitely go for this tool.

Wrap Up

In the blog, we have discussed how to fix the error occurred while we restore the SQL server database when it is in use. We can’t restore it when the database is in use. For this, we need to disconnect the users connected to the server. You can use disconnect the user’s option which shows up in restore option. Using this option, you can notify the users before disconnecting them from the server. However, you can also disconnect the users by restarting the service. But, it is the last option. You can also disconnect the users by switching to single-user mode from the multiple-user mode.

"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.

error - sql 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.

restart sql server service to fix database is in use problem

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.

EaseUS 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 EaseUS 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 EaseUS MS SQL Recovery, click "Browse" (the two dots) or "Search" to choose the location where you saved your corrupted database. Afterward, Click "Repair" to start analyzing the specified database.

select the SQL database file

Step 3. When it has done, choose the files you want to repair and click "Export".

Select the database objects

Step 4. Turn on the SQL service, you can choose to export the repaired files to database to export it as SQL scripts.

How to restore database in use SQL Server?

In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance..
Right-click Databases and select Restore Database....
On the General page, use the Source section to specify the source and location of the backup sets to restore..

What are the reasons a database restore fails?

Database Failure The 3 Most Common Causes.
File Corruption. If one or more files in the database are damaged, they can cause the database to fail at the file level, causing corruption. ... .
File System Damage. ... .
Software and Hardware Failure..

What permissions are needed to restore a SQL Server database?

Permissions. If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to successfully restore the database. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner ( dbo ) of the database.