Solution for SQL Database is in Use Cannot Restore Error

Problem:
SQL Database is in use could not be restored error occurs when the user tries to restore the database from backup file. It can appear due to many reasons like; connection issue, SQL database service issue, user account problem and, database corruption.

Here is some sample for the error message that you may get while restoring your database from backup:

In T-SQL

Msg 3101, Level 16, State 1, Line 5
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.

In SSMS

Solution:

• Close the existing connection of the database: You can use below code to close the existing connection.

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

You can also check the existing connections in SSMS. Check the “Close existing connections to destination database” in Restore Database option.

After completion of it, don’t forget to set your database into the multiuser mode by following commands.
ALTER DATABASE YourDatabaseName
SET MULTI_USER;
GO

• Restart SQL Server Service: Restarting the SQL Service can also fix this problem because service controls the running process. You should maintain service properly to run the SQL Server successfully. See the steps to stop the SQL Server service:
• In the Start Menu, go to All Programs->Microsoft SQL Server->Configuration Tools and click on the SQL Server Configuration Manager.

• Now Expand the service section and select the instance of SQL Server as shown in the figure below:

• Right-click on the instance and stop the service or use the four buttons on the toolbar for Start, Pause, Stop and, Restart.

• Change the Default Database to Master Database: Sometimes, the user cannot login due to default database presence. There are many reasons of this, corruption in database, offline database, and user access problem after renaming the database, etc. to fix this problem. Open SQL Server Management Studio (SSMS) to change the default database to master database.
• Open SSMS, File->Connect Object Explorer

•  Now fill all the details and click on the Options>>

• In the Connection Properties section, select master at the place of default and click the connect button.

Users, who are using SQL Server 2008, has another option to change the default database to master database by sp_defaultdb

EXEC sp_defaultdb ‘sqldb’,’master’
Or
ALTER LOGIN sqldb
with DEFAULT_DATABASE = master

Where sqldb is my database name. This option won’t work on the later versions of SQL Server 2008.

• Take Another Backup: It's possible that, you have an issue with .BAK file. In this case, you can create another backup of your database and try to restore from the new backup. To create the new backup:
• Using SSMS:
• Open SSMS, right click on the database
• Select Task->Back up.
• Select backup type as “Full”
• Select “Disk” as the backup destination
• Click on “Add” button to add file and click “OK”.
• Click “OK” to create the backup
• Using T-SQL:

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK'
GO

• One more solution for this Error: If the problem still exists and you are unable to restore the database from new backup then the problem should be in the backup file. In this case, you can download Stellar Toolkit for MS SQL. The toolkit consists three software: SQL repair, SQL backup recovery and, SQL password recovery. You can restore the SQL database from the corrupt .BAK file and supports MS SQL Server 2016 and all lower versions. Check the steps to repair the corrupt .BAK file:
• Download and install the software.
• Select .BAK file or search in the particular folder or search in the particular drive.
• Now click on the “Scan” button and start scanning process.

Advantages of the software:

• Preview facility is available in the demo version.
• Restores all database objects.
• To repair the file and see the preview, there is no requirement of SQL Server installation on the machine.
• Multiple saving options like XLS, HTML and, CSV.

Conclusion: These methods are very helpful and easy to understand. To fix this problem, you need to know the exact reason behind it.

Too many people stuck in this problem and ready to pay for it. Eventually, the tool, which I discussed above, is like a one-time investment for SQL database users with free support.

After reading this blog, you have the solutions to fix SQL database is in use could not be restored error and if, you know more about its fixes then share with me. I am always eager to learn new things.