SQL-Server: Error - Exclusive access could not be obtained because the database is in use

Sql Sql Server Sql Server-2008

Sql Problem Overview

I am actually trying to make a script (in Sql Server 2008) to restore one database from one backup file. I made the following code and I am getting 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 do I 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

Sql Solutions

Solution 1 - Sql

Set the path to restore the file.
Click "Options" on the left hand side.
Uncheck "Take tail-log backup before restoring"
Tick the check box - "Close existing connections to destination database".
Click OK.

Solution 2 - Sql

I'll assume that if you're restoring a db, you don't care about 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 additional item to be aware. After you set the db into single user mode, someone else may attempt to connect to the db. If they succeed, you won't be able to proceed with your restore. It's a race! My suggestion is to run all three statements at once.

Solution 3 - Sql

execute this query before restoring database:

alter database [YourDBName] 
set offline with rollback immediate

and this one after restoring:

  alter database [YourDBName] 
  set online

Solution 4 - Sql

For me, the solution is:

Check Overwrite the existing database(WITH REPLACE) in optoins tab at left hand side.
Uncheck all other options.
Select source and destination database.
Click ok.

That's it.

Solution 5 - Sql

Use the following script to find and kill all the opened connections to the database before restoring 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 will help...

Solution 6 - Sql

taking original db to offline worked for me

Solution 7 - Sql

I just restarted the sqlexpress service and then the restore completed fine

Solution 8 - Sql

I think you just need to set the db to single user mode before attempting to restore, like below, just make sure you're using master

USE master
GO
ALTER DATABASE AdventureWorksDW
SET SINGLE_USER

Solution 9 - Sql

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

Solution 10 - Sql

Setting the DB to single-user mode didn't work for me, but taking it offline, and then bringing it back online did work. It's in the right-click menu of the DB, under Tasks.

Be sure to check the 'Drop All Active Connections' option in the dialog.

Solution 11 - Sql

I experienced this issue when trying to restore a database on MS SQL Server 2012.

Here's what worked for me:

I had to first run the RESTORE FILELISTONLY command below on the backup file to list the logical file names:

RESTORE FILELISTONLY 
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\my_db_backup.bak'

This displayed the LogicalName and the corresponding PhysicalName of the Data and Log files for the database respectively:

LogicalName		 PhysicalName				
com.my_db	     C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\com.my_db.mdf
com.my_db_log	 C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\com.my_db_log.ldf

All I had to do was to simply replace the LogicalName and the corresponding PhysicalName of the Data and Log files for the database respectively in my database restore script:

USE master;
GO

ALTER DATABASE my_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

    
RESTORE DATABASE my_db
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\my_db_backup.bak'
    WITH REPLACE,
    MOVE 'com.my_db' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\com.my_db.mdf',
    MOVE 'com.my_db_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\com.my_db_log.ldf'
GO
    
ALTER DATABASE my_db SET MULTI_USER;
GO

And the Database Restore task ran successfully:

That's all.

I hope this helps

Solution 12 - Sql

Here's a way I am doing database restore from production to development:

NOTE: I am doing it via SSAS job to push production database to development daily:

Step1: Delete previous day backup in development:

declare @sql varchar(1024);

set @sql = 'DEL C:\ProdAEandAEXdataBACKUP\AE11.bak'
exec master..xp_cmdshell @sql

Step2: Copy production database to development:

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

Step3: Restore by running .sql script

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

Code that is within 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;

Solution 13 - Sql

I got this error when there was not enough disk space to restore Db. Cleaning some space solved it.

Solution 14 - Sql

Solution 1 : Re-start SQL services and try to restore DB Solution 2 : Re-start system / server and try to restore DB Solution 3 : Take back of current DB, Delete the current/destination DB and try to restore DB.

Solution 15 - Sql

I got this error when unbeknownst to me, someone else was connected to the database in another SSMS session. After I signed them out the restore completed successfully.

Content Type	Original Author	Original Content on Stackoverflow
Question	Steam	View Question on Stackoverflow
Solution 1 - Sql	Vinu M Shankar	View Answer on Stackoverflow
Solution 2 - Sql	Dave Mason	View Answer on Stackoverflow
Solution 3 - Sql	Andrei Karcheuski	View Answer on Stackoverflow
Solution 4 - Sql	Aayush Verma	View Answer on Stackoverflow
Solution 5 - Sql	Aqeel Haider	View Answer on Stackoverflow
Solution 6 - Sql	Ali Karaca	View Answer on Stackoverflow
Solution 7 - Sql	BabaNew	View Answer on Stackoverflow
Solution 8 - Sql	Jason	View Answer on Stackoverflow
Solution 9 - Sql	Puneeth	View Answer on Stackoverflow
Solution 10 - Sql	Jeffrey Harmon	View Answer on Stackoverflow
Solution 11 - Sql	Promise Preston	View Answer on Stackoverflow
Solution 12 - Sql	NonProgrammer	View Answer on Stackoverflow
Solution 13 - Sql	user3790083	View Answer on Stackoverflow
Solution 14 - Sql	Joseph Joy	View Answer on Stackoverflow
Solution 15 - Sql	derek morrison	View Answer on Stackoverflow