Alter Database Failed Because a Lock Could Not Be Placed on Database mdsdb Try Again Later

Summary: This web log will describe solutions to fix the 'SQL database restore failed, database in employ' mistake. These solutions include disconnecting agile connections (users and processes) to the database and past using Stellar Toolkit for MS SQL software to recover SQL database (db) from corrupt db or backup (.bak) file. The solutions use to MS SQL Server 2019, 2017, 2016, 2014 and lower versions.

When trying to restore SQL Server database from backup, it is mutual to receive an error message that reads equally follows:

Restored failed for Server 'xxx' (Microsoft.SqlServer.SmoExtended)

Additional Data: System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use.

Figure i: SQL Database Restore Failed Error Bulletin

Reasons behind 'SQL Database Restore Failed, Database in Use' Error

Below are some reasons that could interfere with the restore process and throw the 'restore of database failed because the database is in employ' error:

• Y'all are connected to the database you are trying to restore.
• While using SQL Server Management Studio (SSMS) to do a database restore, yous have more than than 1 window open in it.
• Other users are continued to the master db.

Now, we volition 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. Utilise Stellar Toolkit for MS SQL software that comes with an efficient SQL backup extractor tool designed to aid database administrators recover SQL database from corrupted backup (.BAK) file. The software supports SQL Server 2019, 2017, 2016, 2014, 2012, & older versions.

Before We Begin

Before proceeding with resolving the mistake – exclusive admission could non be obtained considering the database is in use, brand sure to meet the following prerequisites:

• SQL Server, of any version, must be installed on your system.
• Y'all will need SQL Server Management Studio (SSMS) installed on your computer.

How to Set 'can't restore SQL database considering it is in use' Trouble?

When attempting to restore SQL Server db, brand sure in that location are no active connections. If someone is using the database, the restore performance volition neglect. To resolve the consequence, y'all will need to disconnect the active users. You can exercise and then, by post-obit any of these methods:

NOTE: Before disconnecting the users, use SQL stored process 'sp_who' to check all users currently using the db. If you find users performing some important tasks, notify those users earlier disconnecting them. For detailed information on sp_who, refer to this link . If you don't desire to notify users, skip to method 2.

Method 1 – Close the existing connections to the database

To close existing connections to SQL db, follow these steps:

Pace 1: Open SSMS and connect to the db.

Step ii: After connecting to the database, Object Explorer console volition appear on the left side of the SSMS window.

Step 3: In Object Explorer panel, right-click Databases, and then select Restore Database.

Figure 2: Restore Database

Pace four: In Restore Database dialog box, do the following:

• Select one of the databases to restore.
• In the left panel, click Options.

Pace five: In Options page, bank check the checkbox labeled, 'shut existing connections to destination database'.

Figure 3: Close Existing Connections

Once the SQL Server connections are airtight, go along with the restore operation.

Method two –Modify from multiple-user mode to single-user way

Changing the multiple-user mode by default to unmarried user mode will disconnect all the connected users. This option tin 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 way to unmarried-user mode by following these steps:

Pace 1: Open SSMS, connect to the database.

Stride 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:

Apply master; Get Modify DATABASE AdventureWorks2012 SET SINGLE_USER WITH ROLLBACK IMMEDIATE; Go

Figure 4: SSMS Query Editor

Executing the above code will change the database to single-user style.

Method three – Restart the SQL Server Service

You lot can besides disconnect the users by restarting the SQL service. You can restart the service by using SQL Server Configuration Manager, SSMS, services panel, or the command line.

Notation: Use this method as a terminal resort. That's because, you may merely 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:

Pace 1: Browse SQL Server Configuration Director 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 End and Starting time information technology.

Footstep 3: Click OK to go out the SQL Server Configuration Managing director.

Figure 5: SQL Server Configuration Manager Window

What if the problem still persists?

If the issue withal persists, probable there is a problem with your database or the backup file, used for restoring the database, is decadent. In that case, apply Stellar Toolkit for MS SQL. The software can recover db from a corrupt SQL Server. It can also extract a database – from corrupt fill-in (.bak) files – that demand to be restored.

Stellar Toolkit for MS SQL software tin can also assistance you reset lost or forgotten SQL Server Ambassador and user passwords. You tin read the software review done past MVP from hither.

To restore database from corrupt SQL Server backup (.bak) file by using the software, follow these steps:

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

Figure 6: Select Backup (.bak) File

NOTE: Choose 'Search in Folder' option, if you do not know the file location.

Step 4: Subsequently selecting the .bak file, click Scan.

Step five: The BackupSet window appears with details of all the backups.

Figure 7: List of Available Backups

Footstep 6: Choose the .bak file y'all want to recover from the Fill-in Type list, and then click Adjacent to go on with the scanning process.

Step seven: Once scanning is complete, a dialog box appears displaying the number of total records bachelor in the backup file.

Pace 8: The software shows a preview of the database records.

Step nine: To save the recovered .bak file, click Relieve on File bill of fare.

Stride x: In the window that pops-up, choose MSSQL under Save As, and so click Browse to select the location to save the recovered file. Click OK.

Figure 8: Backup File Saving Formats

Step 11: Choose New Database or Live Database under Saving Options. Next, specify details required in Connect to Server section, and and then click Connect.

Effigy 9: Backup File Saving Options

Step 12: Click OK when the 'Recovery process successfully completed' message appears.

Figure 10 – Recovery Complete Message Box

The recovered file will get saved in the selected location.

You can sentinel the complete video from here:

Decision

This weblog explained how to fix the SQL database restore failed, database in utilise problem. You can disconnect active users by closing the existing connections or past changing from multiple-user style to single-user mode. Or, disconnect all the users past restarting the SQL Server service. But, if you nevertheless have issues restoring the db, Stellar SQL Database Toolkit tin can come in handy. It helps resolving the issue by repairing the corrupt SQL db or past recovering the SQL Server fill-in file.

About The Writer

Priyanka

Priyanka is a technology skillful working for central technology domains that revolve around Data Recovery and related software'south. She got expertise on related subjects like SQL Database, Access Database, QuickBooks, and Microsoft Excel. Loves to write on different technology and information recovery subjects on regular basis. Technology freak who always found exploring neo-tech subjects, when non writing, research is something that keeps her going in life.

All-time Selling Products

spearthavervist.blogspot.com

Source: https://www.stellarinfo.com/blog/fix-sql-database-restore-failed-error/