SQL 数据库恢复因移动而失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14953848/
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
database restore failing with move
提问by DotnetSparrow
I am trying to restore a database backup but getting error:
我正在尝试恢复数据库备份但出现错误：
Restore failed for Server 'ASIF-VAIO'. (Microsoft.SqlServer.SmoExtended)
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: File 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\uwa.mdf' is claimed by 'Aston_Fresh_log'(2) and 'Aston_Fresh'(1). The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo)
服务器“ASIF-VAIO”的还原失败。(Microsoft.SqlServer.SmoExtended)
附加信息：
System.Data.SqlClient.SqlError：文件“C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\uwa.mdf”被“Aston_Fresh_log”(2) 和“Aston_Fresh”(1) 声明。WITH MOVE 子句可用于重新定位一个或多个文件。(Microsoft.SqlServer.Smo)
回答by marc_s
When restoring, you need to be sure to
恢复时，您需要确保
- pick a new database name that doesn't already exist (unless you want to overwrite that pre-existing database)
- 选择一个尚不存在的新数据库名称（除非您想覆盖该预先存在的数据库）
- you tick the
Overwriteoption in theOptionstab page and define validand new file names for the.mdfand.ldffile so that you don't accidentally overwrite another database on your system:
- 您勾选
Overwrite选项Options卡页中的选项并为和文件定义有效的新文件名，以便您不会意外覆盖系统上的另一个数据库：.mdf.ldf
回答by TheRealKernel
This post has some excellent answers but I don't believe my solution was covered here, or I didn't understand the answer/comment.
这篇文章有一些很好的答案，但我认为这里没有涵盖我的解决方案，或者我不明白答案/评论。
However, when I encountered this error I was restoring a database with 2 indexes (Primary and Index). The issue was that when restoring it had created two .ndf files, one for each index, but had named them the same thing.
但是，当我遇到此错误时，我正在恢复具有 2 个索引（主索引和索引）的数据库。问题是，在恢复时，它创建了两个 .ndf 文件，每个索引一个，但将它们命名为相同的东西。
So essentially I had two "Restore As" files restoring to "D:\MSSQLDATA\DatabaseName.ndf.
所以基本上我有两个“恢复为”文件恢复到“D:\MSSQLDATA\DatabaseName.ndf。
To overcome this I had to change one of the file names, so for example I changed
为了克服这个问题，我不得不更改其中一个文件名，例如我更改了
Index | D:\MSSQLDATA\DatabaseName.ndf
Primary | D:\MSSQLDATA\DatabaseName1.ndf
having unique file names fixed this for me.
有唯一的文件名为我解决了这个问题。
回答by Lisa
This worked for me : giving a different name to each MDF and LDF file in the script section.
这对我有用：为脚本部分中的每个 MDF 和 LDF 文件指定不同的名称。
MOVE N'R_Data'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Build51_Testing_db1.mdf',
MOVE N'R_audit'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Build51_Testing_db2.mdf',
etc...
等等...
Originally suggested by Alberto Morillo
最初由 Alberto Morillo 建议
回答by Grzegorz Ochlik
I know it's long since the last answer, but I happened to search in google for solution for this problem. What did it for me, was scripting the restore (changing file name did not do the trick) and manually changing the filenames in code
我知道距离上一个答案已经很久了，但我碰巧在谷歌搜索这个问题的解决方案。对我来说是什么，是编写还原脚本（更改文件名没有用）并手动更改代码中的文件名
RESTORE DATABASE [DB_NAME]
FILE = N'[name]',
FILE = N'[name1]',
FILE = N'[name2]'
FROM DISK = N'[file_path]'
WITH FILE = 1m
MOVE N'[name]' TO N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\[name].mdf',
MOVE N'[name1]' TO N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\[name1].mdf',
MOVE N'[name2]' TO N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\[name2].mdf',
MOVE N'[logname]' TO N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\[logname].ldf'
NOUNLOAD,
REPLACE,
STATS = 10
GO
Regards
问候
回答by Dispersia
If you have this issue and it's not the above, try under the Restore Options > Files, check the Relocate all files to folder checkbox.
如果您遇到此问题并且不是上述问题，请尝试在“还原选项”>“文件”下，选中“将所有文件重新定位到文件夹”复选框。
