SQL-Server: 错误 - 无法获得独占访问，因为数据库正在使用中 [英] SQL-Server: Error - Exclusive access could not be obtained because the database is in use

问题描述

我实际上正在尝试制作一个脚本(在 Sql Server 2008 中)以从一个备份文件恢复一个数据库.我编写了以下代码，但出现错误 -

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

推荐答案

我假设如果您正在恢复一个数据库，您不会关心该数据库上的任何现有事务.对?如果是这样，这应该适合您:

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.

这篇关于SQL-Server: 错误 - 无法获得独占访问，因为数据库正在使用中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！