SQL 错误 - 无法获得独占访问权限，因为数据库正在使用中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22209499/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error - Exclusive access could not be obtained because the database is in use
提问by Steam
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 -
我实际上是在尝试制作一个脚本（在 Sql Server 2008 中）来从一个备份文件恢复一个数据库。我编写了以下代码，但出现错误 -
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
采纳答案by Dave Mason
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.
现在，要注意另外一项。将数据库设置为单用户模式后，其他人可能会尝试连接到数据库。如果它们成功，您将无法继续进行恢复。这是一场比赛！我的建议是同时运行所有三个语句。
回答by Vinu M Shankar
- 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.
- 设置恢复文件的路径。
- 单击左侧的“选项”。
- 取消选中“恢复前进行尾日志备份”
- 勾选复选框 - “关闭与目标数据库的现有连接”。
- 单击确定。
回答by Andrei Karcheuski
execute this query before restoring database:
在恢复数据库之前执行此查询：
alter database [YourDBName]
set offline with rollback immediate
and this one after restoring:
恢复后的这个：
alter database [YourDBName]
set online
回答by Aayush Verma
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.
在左侧的 optoins 选项卡中选中 Overwrite the existing database(WITH REPLACE)。
取消选中所有其他选项。
选择源和目标数据库。
单击确定。
That's it.
就是这样。
回答by Aqeel Haider
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...
希望这会有所帮助...
回答by Jason
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
我认为您只需要在尝试恢复之前将数据库设置为单用户模式，如下所示，只需确保您正在使用 master
USE master
GO
ALTER DATABASE AdventureWorksDW
SET SINGLE_USER
回答by Puneeth
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
回答by Jeffrey Harmon
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.
请务必选中对话框中的“删除所有活动连接”选项。
回答by BabaNew
I just restarted the sqlexpress service and then the restore completed fine
我刚刚重新启动了 sqlexpress 服务，然后恢复正常完成
回答by Joseph Joy
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.
解决方案 1：重新启动 SQL 服务并尝试恢复数据库 解决方案 2：重新启动系统/服务器并尝试恢复数据库 解决方案 3：收回当前数据库，删除当前/目标数据库并尝试恢复数据库。
