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

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 2

A restore can only happen if the database does not have any connections to it. 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 3

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";
    }
}