SQL 错误 - 逻辑文件不是数据库的一部分。使用 RESTORE FILELISTONLY 列出逻辑文件名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22245808/
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
Error - Logical file is not part of database. Use RESTORE FILELISTONLY to list the logical file names
提问by Steam
I made a script to restore .bak or backup files. It works for some databases, but not for one. How do I make it work for any type of .bak file ? This is in sql server 2008. The error message is -
我制作了一个脚本来恢复 .bak 或备份文件。它适用于某些数据库，但不适用于某些数据库。我如何使它适用于任何类型的 .bak 文件？这是在 sql server 2008 中。错误信息是 -
Msg 3234, Level 16, State 1, Line 1
Logical file 'Northwind_Data' is not part of database 'Northwind'.
Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Script -
脚本 -
IF DB_ID('Northwind') IS NULL
BEGIN
RESTORE DATABASE [Northwind]
FILE = N'Northwind_Data'
FROM
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\Backup\Northwind.bak'
WITH FILE = 1,
MOVE N'Northwind_Data'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\DATA\Northwind.mdf',
MOVE N'Northwind_Log'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\DATA\Northwind_0.LDF',
NOUNLOAD, STATS = 10
END
回答by Saurabh Sinha
Please run below sql and check logical names
请在sql下面运行并检查逻辑名称
RESTORE FILELISTONLY
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\Backup\Northwind.bak'
And then replace logical name shown by RESTORE FILELISTONLYin script below
然后替换RESTORE FILELISTONLY下面脚本中显示的逻辑名称
--If database already exists do not restore
IF DB_ID('Northwind') IS NULL
BEGIN
RESTORE DATABASE [Northwind]
FILE = N'Northwind_Data'
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\Backup\Northwind.bak'
WITH
FILE = 1, NOUNLOAD, STATS = 10,
MOVE N'YOUR logical name of data file as shown by RESTORE FILELISTONLY command'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\DATA\Northwind.mdf',
MOVE N'YOUR logical name of Log file as shown by RESTORE FILELISTONLY command'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\DATA\Northwind_0.LDF'
END
回答by AssociateDBA
Check the database properties and make sure the logical name is the same as the file name. Use Alter Database command to modify those:
检查数据库属性并确保逻辑名称与文件名相同。使用 Alter Database 命令修改这些：
ALTER DATABASE [Northwind] MODIFY FILE (NAME=N'Northwind_Data', NEWNAME=N'Northwind')
GO
ALTER DATABASE [Northwind] MODIFY FILE (NAME=N'Northwind_Data_log', NEWNAME=N'Northwind_log')
GO
回答by Douglas Loyo
I was using Powershell to do this and I had the same error. What got me was that I was using "$db_log.mdf" and the underscore is a valid character for variable definitions so it was really looking for $db_log and not concatenating.
我正在使用 Powershell 来执行此操作，但遇到了同样的错误。让我知道的是我使用的是“$db_log.mdf”，下划线是变量定义的有效字符，所以它实际上是在寻找 $db_log 而不是连接。
So my code looked like this:
所以我的代码是这样的：
$db = "MyNewDb"
$restoreSuffix = "_V1"
$newDbName = $db
$dataFileOrigin = $db
$logFileOrigin = $db+"_log"
$dataFileLocation = "$dataAndLogFileDestination$db$restoreSuffix.mdf"
$logFileLocation = "$dataAndLogFileDestination$db$restoreSuffix"+"_log.ldf"
echo "Renaming and Relocating files to ($newDbName)"
$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$dataFileOrigin", "$dataFileLocation")
$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$logFileOrigin", "$logFileLocation")
Restore-SqlDatabase -ServerInstance "." -Database "$newDbName" -BackupFile "$backupLocation$db.bak" -RelocateFile @($RelocateData,$RelocateLog)
回答by Allen
I was trying to restore the database from a backup on a UNC path. There were 2 problems:
我试图从 UNC 路径上的备份恢复数据库。有2个问题：
The database started with numbers: 123DbName so this had to be wrapped with [] like [123DbName]
I was writing the full UNC path of server on which I wanted to move: \\server\e$\data | \\server\f$\log, once I removed the server part and kept only e and f everything worked.
数据库以数字开头：123DbName 所以这必须用 [] 包裹，如 [123DbName]
我正在编写要移动的服务器的完整 UNC 路径：\\server\e$\data | \\server\f$\log，一旦我删除了服务器部分并只保留了 e 和 f 一切正常。
回答by SqlACID
What you're asking for is not trivial, and has several potential pitfalls (do you want to overwrite the database if it exists?, what if the database is in use when you try to overwrite? do you want to put the physical files in the same directory all the time? , etc.)..
你所要求的不是微不足道的，并且有几个潜在的陷阱（如果数据库存在，你想覆盖它吗？，如果当你尝试覆盖时数据库正在使用中怎么办？你想把物理文件放在一直在同一个目录？等）。
Luckily it's been asked before, look here, I haven't tested it but it looks sound.
幸运的是之前有人问过，看这里，我还没有测试过，但看起来不错。
