Advertisements
Home > Sql Server > Fixing database is in use error while restore database from backup

Fixing database is in use error while restore database from backup


When performing a full restore over an existing database, SQL Backup sometime report 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.

Workaround

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 only one user can connect to the database at a time.

use master
alter database DB_NAME set SINGLE_USER with rollback immediate;

If problem get resolved by using above then no need to run following query.

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 any number of users who have rights to connect to the database will be able to connect to the database.

use master
alter database DB_NAME set online with rollback immediate;

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

Advertisements
Categories: Sql Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: