Most of the time while restoring database from a backup file, I got an error that my database is in use. To resolve this either I need to find in which application it is being used or I need to restart the SQL Server. In this article I will show how can we overcome this problem without doing any of these.

Introduction

If you are trying to restore a database then primary check for SQL is that it should not be using by any of the user. And if it found so, SQL simply through an error that Database is in use. Let's see how it can be resolved without applying any of above method.

Code

In Sql we have a SETcommand which can be used to restrict access to database. By default it is multi user. We can set it to single user at the time of restoring database.

ALTER DATABASE DatabaseName SET SINGLE_USER

Below is the code to be execute before restoring database

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

And after restoring database we can again set this to multi user like this

ALTER DATABASE DatabaseName SET MULTI_USER

We can also do the same in design mode also by following below steps.

Right Click on your database>Click Properties>Click Option>Set Restrict access to SINGLE_USER

Hope it can help you. Use this code and try yourself.