37

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 ?

Brad
  • 15,361
  • 6
  • 36
  • 57
Mohammad Dayyan
  • 21,578
  • 41
  • 164
  • 232

6 Answers6

55

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
KeithS
  • 70,210
  • 21
  • 112
  • 164
  • My database is in `D:\SQL\RRDB.mdf`, I have to replace `MyDB` with the full path or just `RRDB.mdf` ? – Mohammad Dayyan Oct 29 '10 at 02:47
  • 2
    I've made the database Single_user, but I faced the same issue! – Ahmed Ahmed Nov 26 '13 at 16:29
  • 1
    You have to put the "With Rollback Immediate" on the end of the statement; that immediately rolls back all pending transactions as it drops the connections. Simply setting it to single user will by default keep connections open until the transactions are completed. – KeithS Dec 16 '13 at 17:22
  • 1
    Did not work for me. I get the same error "Exclusive access could not be obtained..." – AH. Sep 07 '15 at 10:37
15

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";
    }
}
Mohammad Dayyan
  • 21,578
  • 41
  • 164
  • 232
  • looks good to me, in principal. I'm not able to run it, but there are no glaring errors. Bear in mind that the 'set single user with rollback immediate' will close all connections (except the one running the command) and rollback all transactions though. – Dave Oct 29 '10 at 09:20
9

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

sqlServer.KillAllProcesses("databaseName");
bluish
  • 26,356
  • 27
  • 122
  • 180
Martin Bell
  • 91
  • 1
  • 1
9

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)

Iman
  • 17,932
  • 6
  • 80
  • 90
  • 2
    SET OFFLINE & SET ONLINE worked on SQL 2008 as well. Note that both must be done in succession; if the DB is offline it can't be restored. – Mark Maslar Jul 15 '12 at 13:27
  • if the DB is offline it can be restored. I test it on updated 2008 R2 and it worked. usually the mentioned methods does not work and you must use activity monitor to kill active sessions. – Iman May 14 '13 at 03:27
1

  • 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
kleopatra
  • 51,061
  • 28
  • 99
  • 211
karthik
  • 11
  • 1
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

Dave
  • 6,905
  • 2
  • 32
  • 35
  • -1 for plagiarism. The text below the EDIT is copied directly from TheOneBlackMage's answer [here](http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/aad41cbb-10cb-4109-9e55-aab048bbeb9d). – Joe Stefanelli Oct 28 '10 at 20:19
  • "Call the internet police, I made a mistake and now I'm quoting websites to try and redeem myself" <- Joe Stefanelli, 28/10/2010 EDIT: He deleted his comment, and left me downvoted. LOL – Dave Oct 28 '10 at 20:32
  • 1. Looking at the edit history, you added the hyperlink *after* I posted my comment. 2. Any idea why I got 4 downvotes on some of my other answers immediately after I posted my comment? – Joe Stefanelli Oct 28 '10 at 20:35
  • Plagiarism (from WIKIPEDIA) : "the wrongful appropriation, close imitation, or purloining and publication, of another author's language, thoughts, ideas, or expressions, and the representation of them as one's own original work." <- does it look like, inside that giant quote, that I'm looking to pass that QUOTE off as my own? Grow up and do somethign productive, like learning what plagiarism means before you start throwing accusations about. – Dave Nov 01 '10 at 09:36