Exclusive access could not be obtained because the database is in use

Question

I'm using following code to restore databases,

void Restore(string ConnectionString, string DatabaseFullPath, string backUpPath)
{
    string sRestore =
        "USE [master] RESTORE DATABASE [" + DatabaseFullPath + "] FROM DISK = N'" + backUpPath + "' WITH  FILE = 1,  NOUNLOAD,  STATS = 10";

    using (SqlConnection con = new SqlConnection(ConnectionString))
    {
        con.Open();
        SqlCommand cmdBackUp = new SqlCommand(sRestore, con);
        cmdBackUp.ExecuteNonQuery();
    }
}

but I receive below exception

"Exclusive access could not be obtained because the database is in use.
RESTORE DATABASE is terminating abnormally.
Changed database context to 'master'."

How can I fix it ?

Answer 1

A restore can only happen if the database does not have any connections to it (besides yours). The easy way on a MS SQL Server to kick all users off is:

ALTER DATABASE [MyDB] SET Single_User WITH Rollback Immediate
GO

Now, you can perform your restore with impunity. Make sure you set it back to Multi-user mode when you're done with the restore:

ALTER DATABASE [MyDB] SET Multi_User
GO

Answer 2

Thus I've written the below method to restore my database,
Am I in right way ?

void Restore(string ConnectionString, string DatabaseFullPath, string backUpPath)
{
    using (SqlConnection con = new SqlConnection(ConnectionString))
    {
        con.Open();

        string UseMaster = "USE master";
        SqlCommand UseMasterCommand = new SqlCommand(UseMaster, con);
        UseMasterCommand.ExecuteNonQuery();

        string Alter1 = @"ALTER DATABASE [" + DatabaseFullPath + "] SET Single_User WITH Rollback Immediate";
        SqlCommand Alter1Cmd = new SqlCommand(Alter1, con);
        Alter1Cmd.ExecuteNonQuery();

        string Restore = @"RESTORE DATABASE [" + DatabaseFullPath + "] FROM DISK = N'" + backUpPath + @"' WITH  FILE = 1,  NOUNLOAD,  STATS = 10";
        SqlCommand RestoreCmd = new SqlCommand(Restore, con);
        RestoreCmd.ExecuteNonQuery();

        string Alter2 = @"ALTER DATABASE [" + DatabaseFullPath + "] SET Multi_User";
        SqlCommand Alter2Cmd = new SqlCommand(Alter2, con);
        Alter2Cmd.ExecuteNonQuery();

        labelReport.Text = "Successful";
    }
}

Answer 3

The best approach

Alter Database <Db_Name>  SET [SINGLE_USER | RESTRICTED_USER] 
With ROLLBACK [IMMEDIATE | AFTER 30]
go
--do your job that needs exclusive access
go
--Back to normal mode
Alter Database <Db_Name> SET MULTI_USER 

• WITH ROLLBACK IMMEDIATE - this option doesn't wait for transactions to complete it just begins rolling back all open transactions

• WITH ROLLBACK AFTER nnn - this option will rollback all open transactions after waiting nnn seconds for the open transactions to complete. In our example we are specifying that the process should wait 30 seconds before rolling back any open transactions.

• When RESTRICTED_USER is specified, only members of the db_owner, dbcreator, or sysadmin roles can use the database. MULTI_USER returns the database to its normal operating state.

---

2nd way :using ssms 2008 R2 we can do the same thing

1. right-click database property
2. go to options -> last section with header of state
3. change Restrict Access to SINGLE_USER
4. answer yes to this helpful question which shows that this kind of action will close all other connections and i guess it is the only thing we are looking for here to by pass the error

To change the database properties, SQL Server must close all other connections to the database. Are you sure you want to change the properties and close all other connections? yes or no

1. restore your database
2. do step 1-4 changing Restrict Access back to MULTI_USER

---

3rd way : the following commands will also close all connections too.

ALTER DATABASE [DbName] SET OFFLINE
go    
ALTER DATABASE [DbName] SET ONLINE

now database is ready for restore

More (mssqltips :Getting exclusive access to restore SQL Server databases)

Answer 4

You can use the method on SMO SqlServer object to kill all processes on a specified database before performing a restore:

sqlServer.KillAllProcesses("databaseName");

Answer 5

The reason for this issue is self-evident (connections to the database currently open/active), but use the following (google it too so you understand it) and it'll be fine:

Alter Database YOURDB   
SET SINGLE_USER With ROLLBACK IMMEDIATE
GO

Obviously, replace YOURDDB with the name of your database and run that against the master DB.

Oh, and just incase, if you get it 'stuck' in single user mode, this will undo it:

Alter Database YOURDB   
SET MULTI_USER With ROLLBACK IMMEDIATE
GO

Hope this helps.

EDIT:

You can also follow this, to see where the connections are from, and other information:

I tested this while having services running that would reconnect to the database. I found you had to set to Single User Mode, then run sp_who2 to see where the one connection was coming from, and note the SPID. You can run the kill command for that SPID and the restore in the same transaction, and it should go through. Here is the sequence I used:

USE MASTER ALTER DATABASE DATABASENAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO

-This will make it so only one connection to the database can be made. -Run the following command to see where any recurring connections to database are coming from.

EXEC SP_WHO2

-Check this list, looking under the DBName column. If the database is listed, check the ProgramName, and HostName column to see who is attempting to connect. -If it is not a service, or other application that would automatically reconnect which can be shut down, note the number in the SPID column to kill the connection, and immediately begin the backup. Replace SPID below with just the number.

KILL SPID RESTORE DATABASE DATABASENAME FROM DISK = 'X:\PATHTO\BACKUP.BAK' GO

-If this completes successfully, we can set the newly restored database back to multi user mode.

ALTER DATABASE DATABASENAME SET MULTI_USER WITH ROLLBACK IMMEDIATE GO

Answer 6

• only one connection to the database can be made. -Run the following command to see where any recurring connections to database are coming from.

  EXEC SP_WHO2
  

• Check this list, looking under the DBName column. If the database is listed, check the ProgramName, and HostName column to see who is attempting to connect.

• If it is not a service, or other application that would automatically reconnect which can be shut down, note the number in the SPID column to kill the connection, and immediately begin the backup. Replace SPID below with just the number.

  KILL SPID RESTORE DATABASE DATABASENAME FROM DISK = 'X:\PATHTO\BACKUP.BAK' GO
  

• If this completes successfully, we can set the newly restored database back to multi user mode.

  ALTER DATABASE DATABASENAME SET MULTI_USER WITH ROLLBACK IMMEDIATE GO