[Error Solved] SQL Database Restore Failed, Database in Use

Introduction

It is a common problem that when we try to restore a database, we receive an error message in SQL Server:

Restored failed for Server

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

In this blog, we will show how to fix this problem related to restoring a database when it is in use. We will explore different solutions to solve this problem.

Requirements

We will need the following requirements:

1. This article requires SQL Server installed. You can use any version.
2. Also, you will need SQL Server Management Studio

Getting started

When we try to restore a database, it is necessary to disconnect all the users. There is an option to close connections when you restore a database. You can use to disconnect all the users:

Use sp_who to check who is using the database

If you do not want to disconnect the users, you can use the sp_who to check the active users. Sp_who or sp_who2 are popular systems stored procedures in SQL Server to find processes and users using the database. This option is useful when you do not want to disconnect the users because they may be doing some important tasks. Therefore, you want to notify them before disconnecting them.

Do not restart the service

It is possible to disconnect all the users by restarting the SQL Server. You can restart the service using the SSMS, using the services console or the command line. However, restarting the service should be the last resource. For more information about restarting SQL Server, refer to this link:

Restarting the SQL Server should be made if there is no other option to solve the problem.

Change from multiple-user mode to single-user mode

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.

In order to change from multiple-user mode to single-user mode in T-SQL, run the following script:

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

The backups are corrupt

Sometimes the database is corrupt and it is not possible to create a backup because the database is corrupt.

There is a pretty nice and simple tool to repair not only the database but also to repair backups if Necessary with an additional advantage of SQL password recovery. This software is the Stellar Toolkit for MS SQL.

The software can restore a corrupt database using the .MDF file or recover the backup using the bak file. If you have problems to restore a backup because the backup is corrupt, this software may help you. Stellar Toolkit for MS SQL software also resets your lost or forgotten SQL Server Administrator and user passwords.

Conclusion

In this article, we saw how to handle the problem to restore a SQL Server database when it is in use. You cannot restore a database when it is in use. You could disconnect users by restarting the service. However, it is a better option to use the disconnect users which is an option included in the restore option. It is also possible to disconnect users by changing from multiple-user mode to single-user mode. Finally, we came across the Stellar SQL Database Toolkit. A tool to restore a corrupted database or a corrupted backup and to reset SQL admin and user passwords. If you have problems to restore your database, this toolkit can be really useful.