CODEWITHSUNDEEP
sqlsql-serverbackup
baaroz
baaroz

Reputation: 19587

restore sql server from .bak file Exclusive access could not be obtained

I am trying to restore my sql using bak file

I am getting error

Exclusive access could not be obtained because the database is in use

I tried 

USE [master]
ALTER DATABASE myDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

and run the query

USE [master] RESTORE DATABASE  myDB
   FROM DISK = 'C:\MyDatabase.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10

I also tried from restore wizard with same result.

Exclusive access could not be obtained because the database is in use.

Upvotes: 20

Views: 53991

Answers (6)

billw
billw

Reputation: 120

No solution here worked for me, in the end I reset the SQL Server service itself, after which the restore worked instantly.

Upvotes: 0

kenji
kenji

Reputation: 11

None of the above solution did not work for me. After many trials and errors, I stopped SQL Server Browser and then the restore completed successfully

Upvotes: 0

Craig
Craig

Reputation: 151

Anyone that has had the issues listed above, and none of the advice works.. Just turn off the Taillog backup under 'options'..

Setting (or leaving) this option on will attempt to take a tail-log of the source database itself (even if your source for the restore is just a file). So if the source database is in use (which if you are doing a copy of a production DB will normally be the case) then the restore fails.

Upvotes: 15

Evan M
Evan M

Reputation: 2611

I had this issue when I was trying to restore a production backup to a dev server that already had the database there. I wanted to restore as a copy, which I did by changing the target database name, but the issue was actually with the files. By default, it was trying to overwrite the files that were already there. I fixed the issue by checking the "Relocate all files to folder" in the "Files" page of the restore dialog and choosing a new directory so there wouldn't be file collisions.

Upvotes: 0

Hamed Rezaei
Hamed Rezaei

Reputation: 388

Don't need to write any query to solve this problem. I had the same problem several times and solve it by this method: when you are restoring database

  1. Go to Option tab in Restore database window
  2. Check (Overwrite the existing database (WITH REPLACE))
  3. Check (Close existing connections to destination database)
  4. Then click OK

Restore database is starting...

Upvotes: 19

MAS
MAS

Reputation: 311

Method 1

    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...'

Method 2

  alter database database_name
  set offline with rollback immediate
  alter database database_name
  set online

go

Upvotes: 20

Related Questions