SQL 从不同的数据库备份/恢复导致恢复失败无法获得独占访问
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20674720/
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
Backup/Restore from different database causing Restore failed exclusive access could not be obtained
提问by user960567
I have a database A. I have taken a backup of database A called A.bak. I created a new database B. Now, I right click and Restore B from A.bak. In the Restore Dialog, I checked overwrite existing database and change the LogicalFileName from C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\DATA\A.mdfto C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\DATA\B.mdfand did the same with ldf file. But I am getting
我有一个数据库 A。我备份了一个名为 A.bak 的数据库 A。我创建了一个新的数据库 B。现在，我右键单击并从 A.bak 恢复 B。在“还原”对话框中，我检查了覆盖现有数据库并将 LogicalFileName 从 更改C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\DATA\A.mdf为C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\DATA\B.mdf并对 ldf 文件执行相同操作。但我越来越
Exclusive access could not be obtained because the database is in use.
Exclusive access could not be obtained because the database is in use.
Also tried,
也试过了，
ALTER DATABASE [B] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
Also sp_who2, there was no existing connection of [B]
同样是 sp_who2，没有 [B] 的现有连接
回答by Prof Von Lemongargle
A cause for the attempt to get exclusive access comes from the options page of the restore dialog in SQL Server 2012 Management Studio. It will turn on tail-log and leave in restoring state options for the SOURCE database. So, it will try to gain exclusive access to the source database (in this case A) in order to perform this action. If you turn off the tail log option, you will find that the operation works much more smoothly.
尝试获取独占访问权限的原因来自 SQL Server 2012 Management Studio 中还原对话框的选项页面。它将打开尾日志并保留 SOURCE 数据库的恢复状态选项。因此，它将尝试获得对源数据库（在本例中为 A）的独占访问权限以执行此操作。如果关闭尾部日志选项，您会发现操作更加顺畅。
回答by user960567
The answer was very simple,
答案很简单，
Run this command to grab the LogicalNames,
运行此命令以获取 LogicalNames，
RESTORE FILELISTONLY FROM DISK = 'C:\Users\MyUSer\Desktop\A.bak'
Then Just put the in LogicalName in below,
然后只需将逻辑名称放在下面，
RESTORE DATABASE B
FROM DISK = 'C:\Users\MyUSer\Desktop\A.bak'
WITH
MOVE 'LogicalName' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\Data\B.mdf',
MOVE 'LogicalName_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\Data\B.ldf'
GO
Note you might need to change the path. Helpful links,
请注意，您可能需要更改路径。有用的网址，
How to restore to a different database in sql server?
回答by Saim Boy
B. 'Restore Database' Dialog will be displayed on the General page 1. The name of the restoring database appears in the To database list box. To create a new database, enter its name in the list box.
B. 'Restore Database' 对话框将显示在 General 页面 1. 恢复数据库的名称出现在 To database 列表框中。要创建新数据库，请在列表框中输入其名称。
Select ‘From device'
Click button to display ‘Specify Backup' Dialog
Click ‘Add' to browse the .bak file from the directory and click OK
选择“从设备”
单击按钮以显示“指定备份”对话框
单击“添加”从目录中浏览 .bak 文件，然后单击“确定”
