SQL Database Restore Failed, Database in Use [Solved]

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.

Realted Post