实际上,我正在尝试编写一个脚本(在Sql Server2008中),以便从一个备份文件中恢复一个数据库。我写了下面的代码,我得到一个错误-
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.
如何解决此问题?
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
发布于 2014-03-06 05:34:35
我假设如果您正在恢复一个数据库,那么您并不关心该数据库上的任何现有事务。对吗?如果是这样的话,这对你应该是有效的:
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
现在,还有一件事需要注意。将数据库设置为单用户模式后,其他用户可能会尝试连接到数据库。如果它们成功,您将无法继续进行还原。这是一场比赛!我的建议是一次运行所有三个语句。
发布于 2015-10-21 19:20:54
在恢复数据库之前执行此查询:
alter database [YourDBName]
set offline with rollback immediate
这是恢复后的一个:
alter database [YourDBName]
set online
发布于 2015-09-02 19:28:13
在恢复数据库之前,使用以下脚本查找并终止所有打开的数据库连接。
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...'
希望这能有所帮助..。
https://stackoverflow.com/questions/22209499
复制相似问题