Summary:- While trying to re-establish the SQL Server database file from a backup file, it is usual that users might encounter the inconsistency error mentioned. Restored failed for Server ‘xxx’ (Microsoft.SqlServer.SmoExtended) Additional Information: System.Data.SqlClient.SqlError: Exclusive SQL server restore failed because the database is in use.
Let’s observe the information. It will help us to repair the SQL Database Restore Failed, and Database in Use errors using manual methods and professional SQL Database Recovery.
Why does the Inconsistency issue ‘SQL Database Restore Failed, Database in Use’ occur?
Below are some reasons that might interrupt the re-establishing process. Then you might receive a database inconsistency error in SQL Server i.e. ‘Database restoration failed because the database was in use’.
- Your database file is synchronized during the reestablishing process.
- There might be more than one Windows running in the background while working with the SQL Server Management Studio (SSMS) to restore a database file.
- More than one user is utilizing the master DB.
Now, Let's discuss the various alternatives to resolve the error.
Prerequisites
You must make sure that you have some things prepared before resolving an issue.
- A SQL server must be downloaded on your local machine.
- Also, to resolve the error a relevant SQL Server Management Studio (SSMS)must be installed on your PC.
Now, moving forward to the solutions to resolve the error ‘’SQL server restore failed because the database is in use’’.
Also Read: How to Fix SQL Database in Recovery Mode?
How to Repair ‘can’t restore SQL database because it is in use’ Issue?
While restoring the SQL Server DB, remember no database should be connected within. If there is any database that seems to be running in the background. It will fail the restoration process eventually. Also, to repair the inconsistency issue, disable the connected user from the database file:
NOTE: To confirm if any user is connected with the database run the following command ‘sp_who’. If you observe any user performing an unfinished task, inform them about disconnecting them for a while. For more information look at link sp_who.
Method 1 – Disconnect the database from existing connections
To disconnect from existing connections of SQL DB, follow the below-mentioned procedure.
Step 1: Launch SSMS and synchronize it to the Database.
Step 2: After synchronization, the Object Explorer pane will display on the left side of the SSMS dialogue box.
Step 3: In the Object Explorer pane, right-click the Database file, and then choose Restore Database.
Step 4: In the Restore Database dialogue box, perform the following instructions:
- Pick one of the database files you wish to re-establish.
- In the left window pane, go to the Options tab.
Step 5: In the Options window, tick the checkbox named ‘close existing connections to destination database’.
After all the SQL Server connections are disabled, continue with the restoration process.
Method 2 – Switch from single mode to multiple-user mode
Step 1: Launch SSMS on your local machine and associate it with the database.
Step 2: In the Object Explorer pop-up, choose New Query. Copy and paste the below T-SQL code segment into the query pane, and then hit the Execute tab.
Running the above commands, you will be able to switch from multiple-user mode to single mode.
Method 3 – Reboot the SQL Server Service
NOTE: Opt for this method only if the above two don't work properly. In most cases, the user feels the need to restore a single database. Rebooting the server might disable the connection to all database files.
Below- mentioned are the steps to reboot the SQL Server Configuration Service)service:
Step 1: Navigate SQL Server Configuration Manager with the help of the following path locations:
SQL Server 2019: C:\Windows\SysWOW64\SQLServerManager15.msc
SQL Server 2017: C:\Windows\SysWOW64\SQLServerManager14.msc
SQL Server 2016: C:\Windows\SysWOW64\SQLServerManager13.msc
SQL Server 2014: C:\Windows\SysWOW64\SQLServerManager12.msc
SQL Server 2012: C:\Windows\SysWOW64\SQLServerManager11.msc
Step 2: In the SQL Server Configuration Manager left-side panel, opt for the SQL Server Services. Likewise, in the right pane, right-click on the SQL Server service, and stop and reboot it.
Step 3: Hit the OK button to close the SQL Server Configuration Manager.
Restore of Database failed in SQL Server 2014 Professionally?
If it has been a tedious job to work with the above manual process, the user can utilize a third-party solution instead. One such exception is SQL Database Recovery. The utility can be used to recover the corrupted database files including the error ‘SQL server restore failed because the database is in use’. The tool aims to recover SQL file objects, recovers MDF files using two different modes of handling corruption, claim supporting ROW as well as PAGE compression, fix MDF as well as PDF files of SQL database, etc.
Summing It Up
Lastly, Restoring a SQL Server database may fail with a "SQL Database Restore Failed, Database in Use" error because of active connections or synchronization issues. Common causes include multiple Windows sessions, users accessing the master database, or ongoing connections. To resolve this, run sp_who to check for active connections and disconnect them via SQL Server Management Studio (SSMS) by selecting "Close existing connections" in the Restore Database options. Alternatively, switch to single-user mode using T-SQL commands or reboot the SQL Server service. If these steps don't work, use a third-party tool like SQL Database Recovery to handle corrupted files and restore your database effectively.