Please enable Javascript for better experience...
SQL Server Database restore error-Database is in use
By Big | Sep 22, 2014 | In Articles | Update: Sep 22, 2014 | Total Views [ 1256 ]
Taged In
(3 Like)
Rate

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.

Share this

About the Author

Big
Rahul Jha (Big)
Founder, Developer dotnet-concept.com
Has working experience in different phases of Software Development Life Cycle (SDLC) in CMS, Gaming, Health Care and Financial Services domain using Agile pattern. Working experience in Design patterns, ASP.NET, MVC, Windows application, WCF, ADO.NET, SQL Server and Test Driven Development (TDD) environment with JQuery, JavaScript, N-Unit, Entity Frameworks, LINQ, Code Refactoring and Business Objects Models.

 
Please SignUp/Login to comment...

Or comment as anonymous...
* Name
* Email ID
Comment