Mistake. Exclusive access cannot be obtained because the database is being used.

Question

I am trying to make a script (in Sql Server 2008) to restore one database from one backup file. I made the following code and I get an error -

Msg 3101, Level 16, State 1, Line 3 Exclusive access could not be obtained because the database is in use. Msg 3013, Level 16, State 1, Line 3 RESTORE DATABASE is terminating abnormally. 

How to fix this problem?

 IF DB_ID('AdventureWorksDW') IS NOT NULL BEGIN RESTORE DATABASE [AdventureWorksDW] FILE = N'AdventureWorksDW_Data' FROM DISK = N'C:\Program Files\Microsoft SQL Server\ MSSQL10_50.SS2008\MSSQL\Backup\AdventureWorksDW.bak' WITH FILE = 1, MOVE N'AdventureWorksDW_Data' TO N'C:\Program Files\Microsoft SQL Server\ MSSQL10_50.SS2008\MSSQL\DATA\AdventureWorksDW.mdf', MOVE N'AdventureWorksDW_Log' TO N'C:\Program Files\Microsoft SQL Server\ MSSQL10_50.SS2008\MSSQL\DATA\AdventureWorksDW_0.LDF', NOUNLOAD, STATS = 10 END 

Answer 1

I assume that if you restore db, you do not need any existing transactions on that db. Right? If so, this should work for you:

 USE master GO ALTER DATABASE AdventureWorksDW SET SINGLE_USER --This rolls back all uncommitted transactions in the db. WITH ROLLBACK IMMEDIATE GO RESTORE DATABASE AdventureWorksDW FROM ... ... GO 

Now one more thing to know. After you set db to single user mode, someone else may try to connect to db. If they succeed, you cannot continue the recovery. This is a race! My suggestion is to run all three statements at once.

Answer 2

• Set the path to restore the file.
• Click "Options" on the left.
• Check the box - "Close existing connections to the destination database."
• Click OK.

Hope this works.

Answer 3

Run this query before restoring the database:

 alter database [YourDBName] set offline with rollback immediate 

and this after recovery:

  alter database [YourDBName] set online 

Answer 4

Use the following script to find and kill all open database connections before restoring the database.

 declare @sql as varchar(20), @spid as int select @spid = min(spid) from master..sysprocesses where dbid = db_id('<database_name>') and spid != @@spid while (@spid is not null) begin print 'Killing process ' + cast(@spid as varchar) + ' ...' set @sql = 'kill ' + cast(@spid as varchar) exec (@sql) select @spid = min(spid) from master..sysprocesses where dbid = db_id('<database_name>') and spid != @@spid end print 'Process completed...' 

Hope this helps ...

Answer 5

I think you just need to set db in one user mode before trying to restore as below, just make sure you use master

 USE master GO ALTER DATABASE AdventureWorksDW SET SINGLE_USER 

Answer 6

For me, the solution is:

• Check the overwrite of the existing database (WITH REPLACE) on the optoins tab on the left.

• Clear all other parameters.

• Select the source and destination database.

• Click OK.

What is it.

Answer 7

Here I am doing a database recovery from production to development:

NOTE. I do this through the work of SSAS to release a production database daily:

Step1: deleting the backup of the previous day in development:

 declare @sql varchar(1024); set @sql = 'DEL C:\ProdAEandAEXdataBACKUP\AE11.bak' exec master..xp_cmdshell @sql 

Step 2. Copy the production database to the development:

 declare @cmdstring varchar(1000) set @cmdstring = 'copy \\Share\SQLDBBackup\AE11.bak C:\ProdAEandAEXdataBACKUP' exec master..xp_cmdshell @cmdstring 

Step 3: Repair by running the .sql script

 SQLCMD -E -S dev-erpdata1 -b -i "C:\ProdAEandAEXdataBACKUP\AE11_Restore.sql" 

The code that is in the AE11_Restore.sql file:

 RESTORE DATABASE AE11 FROM DISK = N'C:\ProdAEandAEXdataBACKUP\AE11.bak' WITH MOVE 'AE11' TO 'E:\SQL_DATA\AE11.mdf', MOVE 'AE11_log' TO 'D:\SQL_LOGS\AE11.ldf', RECOVERY; 

Answer 8

 Use Master alter database databasename set offline with rollback immediate; --Do Actual Restore RESTORE DATABASE databasename FROM DISK = 'path of bak file' WITH MOVE 'datafile_data' TO 'D:\newDATA\data.mdf', MOVE 'logfile_Log' TO 'D:\newDATA\DATA_log.ldf',replace alter database databasename set online with rollback immediate; GO