Click here to Skip to main content
13,594,789 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


8 bookmarked
Posted 30 Nov 2011
Licenced CPOL

Fixing database Is In Use Error While Restoring Database from Backup

Rate this:
Please Sign up or sign in to vote.
Fixing database is in use error while restoring database from backup


When performing a full restore over an existing database, SQL Backup sometimes reports that the database cannot be restored because it is currently in use, like shown below:

This error shows not only during restore backup, also for drop database or alter database as well.


In this situation, database administrator needs to disconnect all the connected users from a SQL Server Database to get exclusive access of the database. In the situation where a full restore is blocked because users are currently connected to the database, the best solution to this problem is to take the database offline prior to running the restore, as this is the easiest way to kill all connections to the database.

Before Restore Attempt

Database administrator can execute the below mentioned TSQL command to get a Single User access to a database. SINGLE_USER WITH ROLLBACK IMMEDIATE command is used so that only one user can connect to the database at a time.

use master
alter database DB_NAME set offline with rollback immediate;

After Restore

Database administrator can execute the below mentioned TSQL command to give Multiple User access to a database. MULTI_USER command is used by any number of users who have rights to connect to the database that will be able to connect to the database.

use master
alter database DB_NAME set online with rollback immediate;

So now all authenticated database users can connect to the database again.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Shahriar Iqbal Chowdhury/Galib
Chief Technology Officer
Bangladesh Bangladesh
I am a Software Engineer and Microsoft .NET technology enthusiast. Professionally I worked on several business domains and on diverse platforms. I love to learn and share new .net technology and my experience I gather in my engineering career. You can find me from here

Personal Site
Personal Blog
FB MS enthusiasts group
About Me

You may also be interested in...


Comments and Discussions

GeneralMy vote of 5 Pin
Krekkon10-Apr-14 1:19
memberKrekkon10-Apr-14 1:19 
Questionif its possilbe i would give 10 Pin
write2varun14-Sep-13 3:03
memberwrite2varun14-Sep-13 3:03 
GeneralMy vote of 5 Pin
Rubaba11-Dec-11 0:32
memberRubaba11-Dec-11 0:32 
GeneralRe: My vote of 5 Pin
Shahriar Iqbal Chowdhury12-Dec-11 8:18
memberShahriar Iqbal Chowdhury12-Dec-11 8:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web04 | 2.8.180621.3 | Last Updated 30 Nov 2011
Article Copyright 2011 by Shahriar Iqbal Chowdhury/Galib
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid