无法从.bak文件中恢复sql服务器的独占访问

I am trying to restore my sql using bak file

我正在尝试使用bak文件恢复我的sql

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.

由于数据库正在使用，所以无法获得独占访问。

4 个解决方案

#1

Method 1

方法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

方法2

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

去

#2

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

不需要编写任何查询来解决这个问题。我遇到过同样的问题好几次，用这种方法来解决:当你在还原数据库时

Go to Option tab in Restore database window
转到“恢复数据库”窗口中的“选项”选项卡
Check (Overwrite the existing database (WITH REPLACE))
检查(覆盖现有数据库(替换))
Check (Close existing connections to destination database)
检查(关闭与目标数据库的现有连接)
Then click OK
然后单击OK

Restore database is starting...

恢复数据库开始……

#3

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.

设置(或退出)此选项将尝试获取源数据库本身的尾部日志(即使用于恢复的源只是一个文件)。因此，如果源数据库正在使用(如果您正在复制一个生产DB，通常情况下就是这样)，那么恢复将失败。

#4

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.

当我试图将产品备份恢复到已经有数据库的开发服务器时，我遇到了这个问题。我想还原为一个副本，我通过更改目标数据库名来实现，但问题实际上是文件。默认情况下，它试图覆盖已经存在的文件。我在“恢复”对话框的“文件”页面中检查“将所有文件重新放置到文件夹”，并选择一个新目录，以避免文件冲突，从而解决了这个问题。

#1