Summary: This blog will describe solutions to fix the ‘SQL database restore failed, database in use’ error. These solutions include disconnecting active connections (users and processes) to the database and by using Stellar Toolkit for MS SQL software to recover SQL database (db) from corrupt db or backup (.bak) file. The solutions apply to MS SQL Server 2019, 2017, 2016, 2014 and lower versions.
![[Error Solved] SQL Database Restore Failed, Database in Use](data:image/svg+xml;nitro-empty-id=NzM4OjQxNg==-1;base64,PHN2ZyB2aWV3Qm94PSIwIDAgMjUyIDcyIiB3aWR0aD0iMjUyIiBoZWlnaHQ9IjcyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==)
When trying to restore SQL Server database from backup, it is common to receive an error message that reads as follows:
Restored failed for Server ‘xxx’ (Microsoft.SqlServer.SmoExtended)
Additional Information: System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use.
Contents
Below are some reasons that could interfere with the restore process and throw the ‘restore of database failed because the database is in use’ error:
Now, we will discuss solutions to fix the error.
| Tip: SQL Server database can be restored from the backup (.bak) file. But, the database restore operation may fail if the .bak file is corrupt. Use Stellar Toolkit for MS SQL software that comes with an efficient SQL backup extractor tool designed to help database administrators recover SQL database from corrupted backup (.BAK) file. The software supports SQL Server 2019, 2017, 2016, 2014, 2012, & older versions. |
Before proceeding with resolving the error – exclusive access could not be obtained because the database is in use, make sure to meet the following prerequisites:
When attempting to restore SQL Server db, make sure there are no active connections. If someone is using the database, the restore operation will fail. To resolve the issue, you will need to disconnect the active users. You can do so, by following any of these methods:
NOTE: Before disconnecting the users, use SQL stored procedure ‘sp_who’ to check all users currently using the db. If you find users performing some important tasks, notify those users before disconnecting them. For detailed information on sp_who, refer to this link. If you don’t want to notify users, skip to method 2.
To close existing connections to SQL db, follow these steps:
Step 1: Open SSMS and connect to the db.
Step 2: After connecting to the database, Object Explorer panel will appear on the left side of the SSMS window.
Step 3: In Object Explorer panel, right-click Databases, and then select Restore Database.
Step 4: In Restore Database dialog box, do the following:
Step 5: In Options page, check the checkbox labeled, ‘close existing connections to destination database’.
Once the SQL Server connections are closed, proceed with the restore operation.
Changing the multiple-user mode by default to single user mode will disconnect all the connected users. This option can be used, if you want to disconnect all the users without notifying them.
To force users to go offline (i.e. disconnect) from SQL Server, set the db from multiple-user mode to single-user mode by following these steps:
Step 1: Open SSMS, connect to the database.
Step 2: In Object Explorer window, select New Query. Copy and paste the below T-SQL code snippet into the query window, and then click Execute:
USE master; GO ALTER DATABASE AdventureWorks2012 SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO
Executing the above code will change the database to single-user mode.
You can also disconnect the users by restarting the SQL service. You can restart the service by using SQL Server Configuration Manager, SSMS, services console, or the command line.
NOTE: Use this method as a last resort. That’s because, you may only need to restore a single db, but restarting the server will kill connections to all databases.
Steps to restart the service from SQL Server Configuration Manager are as follows:
Step 1: Browse SQL Server Configuration Manager using any of the following path:
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 left pane of SQL Server Configuration Manager window, click SQL Server Services. And in the right pane, right-click SQL Server service, and Stop and Start it.
Step 3: Click OK to exit the SQL Server Configuration Manager.
If the issue still persists, likely there is a problem with your database or the backup file, used for restoring the database, is corrupt. In that case, use Stellar Toolkit for MS SQL. The software can recover db from a corrupt SQL Server. It can also extract a database – from corrupt backup (.bak) files – that need to be restored.
Stellar Toolkit for MS SQL software can also help you reset lost or forgotten SQL Server Administrator and user passwords. You can read the software review done by MVP from here.
To restore database from corrupt SQL Server backup (.bak) file by using the software, follow these steps:
Step 1: Download, install and launch Stellar Toolkit for MS SQL software.
Step 2: In software’s user interface, select Extract from MS SQL Backup.
Step 3: In Stellar Backup Extractor for MS SQL window, click Select File to choose the .bak file.
NOTE: Choose ‘Search in Folder’ option, if you do not know the file location.
Step 4: After selecting the .bak file, click Scan.
Step 5: The BackupSet window appears with details of all the backups.
Step 6: Choose the .bak file you want to recover from the Backup Type list, and then click Next to proceed with the scanning process.
Step 7: Once scanning is complete, a dialog box appears displaying the number of total records available in the backup file.
Step 8: The software shows a preview of the database records.
Step 9: To save the recovered .bak file, click Save on File menu.
Step 10: In the window that pops-up, choose MSSQL under Save As, and then click Browse to select the location to save the recovered file. Click OK.
Step 11: Choose New Database or Live Database under Saving Options. Next, specify details required in Connect to Server section, and then click Connect.
Step 12: Click OK when the ‘Recovery process successfully completed’ message appears.
The recovered file will get saved in the selected location.
You can watch the complete video from here:
This blog explained how to fix the SQL database restore failed, database in use problem. You can disconnect active users by closing the existing connections or by changing from multiple-user mode to single-user mode. Or, disconnect all the users by restarting the SQL Server service. But, if you still have issues restoring the db, Stellar SQL Database Toolkit can come in handy. It helps resolving the issue by repairing the corrupt SQL db or by recovering the SQL Server backup file.
Priyanka is a technology expert working for key technology domains that revolve around Data Recovery and related software's. She got expertise on related subjects like SQL Database, Access Database, QuickBooks, and Microsoft Excel. Loves to write on different technology and data recovery subjects on regular basis. Technology freak who always found exploring neo-tech subjects, when not writing, research is something that keeps her going in life.
In addition to Method# 1 we can check “Replace Existing Database” from the option panel.
This post helped me solve the issue.
I appreciate it, great job!
when I try to backup SQL Server database in PHP following error occurs how to solve it?
Array ( [0] => Array ( [0] => 01000 [SQLSTATE] => 01000 [1] => 4035 [code] => 4035 [2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Processed 312 pages for database ‘qms’, file ‘qms’ on file 2. [message] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Processed 312 pages for database ‘qms’, file ‘qms’ on file 2. ) ) 1
Great! This is helpful.
When I used SP_WHO I found a SELECT query on my DB entry in the result. I then realized I need to either close all query windows that point to my DB or change DB selection to master/another to get rid of this error.
Hope this might be helpful for someone.
While restoring the database from backup file, I got the following error:
Restore failed for server.(Microsoft.SqlServer.SmoExtended).
System.Data.SqlClient.SqlError: The file
‘C:\ProgramFiles\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\payroll_file.mdf’
cannot be overwritten. It is being used by database ‘payroll_file’. (Microsoft.SqlServer.Smo)
Please provide best solution.
To rectify this problem change the file name for .mdf and .ldf which was taken while backup. Follow below steps:
1. Click on Options Under ‘Select a Page’ section
2. Choose the Original file name which you want to provide and restore the DB file as
3. Click on the Browse button and change the file name (payroll_file2.mdf and payroll_file2.ldf)
4. After changing the database file name (mdf & ldf), now restore the database.