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