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