blocks|key|145312|text|我认为在尝试还原之前，您只需将数据库设置为单用户模式，如下所示，只需确保您使用的是master|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|145313|USE+master
GO
ALTER+DATABASE+AdventureWorksDW
SET+SINGLE_USER|code-block|syntax|javascript|145314|entityMap^0|15|6|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

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 <code>master</code>

<pre><code>USE master
GO
ALTER DATABASE AdventureWorksDW
SET SINGLE_USER
</code></pre>

blocks|key|145357|text|我假设如果您正在恢复一个数据库，那么您并不关心该数据库上的任何现有事务。对吗？如果是这样的话，这对你应该是有效的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|145358|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|code-block|syntax|javascript|145359|现在，还有一件事需要注意。将数据库设置为单用户模式后，其他用户可能会尝试连接到数据库。如果它们成功，您将无法继续进行还原。这是一场比赛！我的建议是一次运行所有三个语句。|145360|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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:

<pre><code>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
</code></pre>

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.

blocks|key|145400|text|在恢复数据库之前，使用以下脚本查找并终止所有打开的数据库连接。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|145401|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+'+%2B+cast(@spid+as+varchar)+%2B+'+...'
++++set+@sql+=+'kill+'+%2B+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...'|code-block|syntax|javascript|145402|希望这能有所帮助..。|145403|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Use the following script to find and kill all the opened connections to the database before restoring database.

<pre><code>declare @sql as varchar(20), @spid as int

select @spid = min(spid) from master..sysprocesses where dbid = db_id('&lt;database_name&gt;') 
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('&lt;database_name&gt;') 
 and spid != @@spid
end 

print 'Process completed...'
</code></pre>

Hope this will help...

blocks|key|4651996|text|在恢复数据库之前执行此查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4651997|alter+database+[YourDBName]+
set+offline+with+rollback+immediate|code-block|syntax|javascript|4651998|这是恢复后的一个：|4651999|++alter+database+[YourDBName]+
++set+online|4652000|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

execute this query before restoring database:

<pre><code>alter database [YourDBName] 
set offline with rollback immediate
</code></pre>

and this one after restoring:

<pre><code> alter database [YourDBName] 
 set online
</code></pre>

blocks|key|145455|text|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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|145456|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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
</code></pre>

blocks|key|145496|text|下面是我将数据库从生产环境恢复到开发环境的方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|145497|注意:我正在通过SSAS作业每天将生产数据库推送到开发中：|145498|Step1:删除开发中的前一天备份：|145499|declare+@sql+varchar(1024);

set+@sql+=+'DEL+C:\ProdAEandAEXdataBACKUP\AE11.bak'
exec+master..xp_cmdshell+@sql|code-block|syntax|javascript|145500|Step2:将生产数据库复制到开发中：|145501|declare+@cmdstring+varchar(1000)
set+@cmdstring+=+'copy+\\Share\SQLDBBackup\AE11.bak+C:\ProdAEandAEXdataBACKUP'
exec+master..xp_cmdshell+@cmdstring+|145502|Step3:通过运行.sql脚本进行恢复|145503|SQLCMD+-E+-S+dev-erpdata1+-b+-i+"C:\ProdAEandAEXdataBACKUP\AE11_Restore.sql"|145504|AE11_Restore.sql文件中的代码：|145505|RESTORE+DATABASE+AE11
FROM+DISK+=+N'C:\ProdAEandAEXdataBACKUP\AE11.bak'
WITH+MOVE+'AE11'+TO+'E:\SQL_DATA\AE11.mdf',
MOVE+'AE11_log'+TO+'D:\SQL_LOGS\AE11.ldf',
RECOVERY;|145506|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|10|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|11|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|12|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|13|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|14|8|@]|9|@]|A|$]]|$1|Q|3|R|5|H|7|15|8|@]|9|@]|A|$I|J]]|$1|S|3|T|5|6|7|16|8|@]|9|@]|A|$]]|$1|U|3|V|5|H|7|17|8|@]|9|@]|A|$I|J]]|$1|W|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|X|$]]

Here's a way I am doing database restore from production to development:

NOTE: I am doing it via SSAS job to push production database to development daily:

Step1: Delete previous day backup in development:

<pre><code>declare @sql varchar(1024);

set @sql = 'DEL C:\ProdAEandAEXdataBACKUP\AE11.bak'
exec master..xp_cmdshell @sql
</code></pre>

Step2: Copy production database to development:

<pre><code>declare @cmdstring varchar(1000)
set @cmdstring = 'copy \\Share\SQLDBBackup\AE11.bak C:\ProdAEandAEXdataBACKUP'
exec master..xp_cmdshell @cmdstring 
</code></pre>

Step3: Restore by running .sql script

<pre><code>SQLCMD -E -S dev-erpdata1 -b -i "C:\ProdAEandAEXdataBACKUP\AE11_Restore.sql"
</code></pre>

Code that is within AE11_Restore.sql file:

<pre><code>RESTORE DATABASE AE11
FROM DISK = N'C:\ProdAEandAEXdataBACKUP\AE11.bak'
WITH MOVE 'AE11' TO 'E:\SQL_DATA\AE11.mdf',
MOVE 'AE11_log' TO 'D:\SQL_LOGS\AE11.ldf',
RECOVERY;
</code></pre>

blocks|key|145562|text|当没有足够的磁盘空间来恢复Db时，我得到了这个错误。清理一些空间解决了这个问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|145563|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I got this error when there was not enough disk space to restore Db. Cleaning some space solved it.

blocks|key|4652159|text|我刚刚重新启动了sqlexpress服务，然后恢复就正常完成了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4652160|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I just restarted the sqlexpress service and then the restore completed fine

blocks|key|145625|text|解决方案1:重新启动SQL服务并尝试恢复数据库解决方案2:重新启动系统/服务器并尝试恢复数据库解决方案3:取回当前数据库，删除当前/目标数据库并尝试恢复数据库。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|145626|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|4652206|text|将数据库设置为单用户模式对我不起作用，但将其离线，然后将其重新联机确实起到了作用。它位于数据库的右键菜单中的Tasks下。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4652207|确保选中对话框中的“Drop+All+Active+Connections”选项。|4652208|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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.

blocks|key|4652287|text|让原始数据库离线对我来说很有效|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4652288|​|4652289|📷|atomic|offset|length|4652290|4652291|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/eb972a709f1b02f1de7f53cbb69a6819.png|imageAlt^0|0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@$G|V|H|W|1|X]]|A|$]]|$1|I|3|C|5|6|7|Y|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|Q|R|-4]]]]

taking original db to offline worked for me

<a href="https://i.stack.imgur.com/eRrwO.png" rel="noreferrer"><img src="https://i.stack.imgur.com/eRrwO.png" alt="take offline"></a>

blocks|key|4652323|text|当我不知道其他人在另一个SSMS会话中连接到数据库时，我得到了这个错误。在我给他们签名之后，恢复成功完成了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4652324|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I got this error when unbeknownst to me, someone else was connected to the database in another SSMS session. After I signed them out the restore completed successfully.

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 - 

<pre><code>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.
</code></pre>

How do I fix this problem ? 

<pre><code>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
</code></pre>

SQL-Server: Error - Exclusive access could not be obtained because the database is in use

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

实际上，我正在尝试编写一个脚本(在Sql Server2008中)，以便从一个备份文件中恢复一个数据库。我写了下面的代码，我得到一个错误-Msg 3101, Level 16, State 1, Line 3Exclusive access could not be obtained because the datab...

问SQL-Server:错误-无法获得独占访问权限，因为数据库正在使用中
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL-Server:错误-无法获得独占访问权限，因为数据库正在使用中EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL-Server:错误-无法获得独占访问权限，因为数据库正在使用中
EN