SQL 如何从备份文件中检索数据库的逻辑文件名

How can I retrieve the logical file name of the database from backup file

提问by Bipul

I was looking into the steps of how to Restore Database Backup using SQL Script (T-SQL). Here are the steps:

我正在研究如何使用 SQL 脚本 (T-SQL) 还原数据库备份的步骤。以下是步骤：

Database YourDBhas full backup YourBackUpFile.bak. It can be restored using following two steps:

Step 1:Retrieve the logical file name of the database from the backup.

RESTORE FILELISTONLY
FROM DISK = 'D:BackUpYourBackUpFile.bak'
GO

Step 2:Use the values in the LogicalNamecolumn in the following step.

----Make Database to single user Mode
ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

----Restore Database
RESTORE DATABASE YourDB
FROM DISK = 'D:BackUpYourBackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'

数据库YourDB有完整备份YourBackUpFile.bak。可以通过以下两个步骤恢复：

步骤 1：从备份中检索数据库的逻辑文件名。

RESTORE FILELISTONLY
FROM DISK = 'D:BackUpYourBackUpFile.bak'
GO

步骤 2：LogicalName在以下步骤中使用列中的值。

----Make Database to single user Mode
ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

----Restore Database
RESTORE DATABASE YourDB
FROM DISK = 'D:BackUpYourBackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'

I am just having problem on how to get the YourMDFLogicalNameand YourLDFLogicalName. Can any one help me with that?

我只是在如何获取YourMDFLogicalNameand 方面遇到问题YourLDFLogicalName。任何人都可以帮助我吗？

回答by Dalex

DECLARE @Table TABLE (LogicalName varchar(128),[PhysicalName] varchar(128), [Type] varchar, [FileGroupName] varchar(128), [Size] varchar(128), 
            [MaxSize] varchar(128), [FileId]varchar(128), [CreateLSN]varchar(128), [DropLSN]varchar(128), [UniqueId]varchar(128), [ReadOnlyLSN]varchar(128), [ReadWriteLSN]varchar(128), 
            [BackupSizeInBytes]varchar(128), [SourceBlockSize]varchar(128), [FileGroupId]varchar(128), [LogGroupGUID]varchar(128), [DifferentialBaseLSN]varchar(128), [DifferentialBaseGUID]varchar(128), [IsReadOnly]varchar(128), [IsPresent]varchar(128), [TDEThumbprint]varchar(128)
)
DECLARE @Path varchar(1000)='C:\SomePath\Base.bak'
DECLARE @LogicalNameData varchar(128),@LogicalNameLog varchar(128)
INSERT INTO @table
EXEC('
RESTORE FILELISTONLY 
   FROM DISK=''' +@Path+ '''
   ')

   SET @LogicalNameData=(SELECT LogicalName FROM @Table WHERE Type='D')
   SET @LogicalNameLog=(SELECT LogicalName FROM @Table WHERE Type='L')

SELECT @LogicalNameData,@LogicalNameLog

UPDATE

更新

According to Microsoft site:

根据微软网站：

SQL Server files have two names:

logical_file_name

The logical_file_name is the name used to refer to the physical file in all Transact-SQL statements. The logical file name must comply with the rules for SQL Server identifiers and must be unique among logical file names in the database.

os_file_name

The os_file_name is the name of the physical file including the directory path. It must follow the rules for the operating system file names.

SQL Server 文件有两个名称：

逻辑文件名

logical_file_name 是用于在所有 Transact-SQL 语句中引用物理文件的名称。逻辑文件名必须符合 SQL Server 标识符的规则，并且在数据库中的逻辑文件名中必须是唯一的。

os_file_name

os_file_name 是包含目录路径的物理文件的名称。它必须遵循操作系统文件名的规则。

回答by phhlho

logical_file_name: is the name used in Microsoft SQL Server when referencing the file. The name must be unique within the database and conform to the rules for identifiers. The name can be a character or Unicode constant, a regular identifier, or a delimited identifier.

logical_file_name：是 Microsoft SQL Server 中引用文件时使用的名称。该名称在数据库中必须是唯一的，并且符合标识符规则。名称可以是字符或 Unicode 常量、常规标识符或分隔标识符。

From: http://msdn.microsoft.com/en-us/library/aa275464(v=sql.80).aspx

来自：http: //msdn.microsoft.com/en-us/library/aa275464(v=sql.80).aspx

Also, from Dalex's script, you can just run (without all the table logic):

此外，从 Dalex 的脚本中，您可以运行（没有所有表逻辑）：

RESTORE FILELISTONLY FROM DISK = 'D:\MyBackups\Backup.bak'

回答by Koka

If you have the original DB (from which the backup file was taken) then the easiest way to obtain its logical name is via :

如果您拥有原始数据库（从中获取备份文件），那么获取其逻辑名称的最简单方法是通过：

use [original_db]
go
select file_name(1)
go

or with one T-SQL

或使用一个 T-SQL

> sqlcmd [connection parameters and credentials] -d [original_db] -Q "set nocount on; select file_name(1)" -h-1 -W 

This would be the same logical_name you would use in the RESTORE command.

这与您在 RESTORE 命令中使用的 logical_name 相同。

回答by Sheridan

Updated version of script:

脚本的更新版本：

DECLARE @Table TABLE (
    LogicalName varchar(128),
    [PhysicalName] varchar(128), 
    [Type] varchar, 
    [FileGroupName] varchar(128), 
    [Size] varchar(128),
    [MaxSize] varchar(128), 
    [FileId]varchar(128), 
    [CreateLSN]varchar(128), 
    [DropLSN]varchar(128), 
    [UniqueId]varchar(128), 
    [ReadOnlyLSN]varchar(128), 
    [ReadWriteLSN]varchar(128),
    [BackupSizeInBytes]varchar(128), 
    [SourceBlockSize]varchar(128), 
    [FileGroupId]varchar(128), 
    [LogGroupGUID]varchar(128), 
    [DifferentialBaseLSN]varchar(128), 
    [DifferentialBaseGUID]varchar(128), 
    [IsReadOnly]varchar(128), 
    [IsPresent]varchar(128), 
    [TDEThumbprint]varchar(128),
    [SnapshotUrl]varchar(128)
)
DECLARE @Path varchar(1000)='/path/to/backup.bak'
DECLARE @LogicalNameData varchar(128),@LogicalNameLog varchar(128)
INSERT INTO @table
EXEC('
RESTORE FILELISTONLY
   FROM DISK=''' +@Path+ '''
   ')

   SET @LogicalNameData=(SELECT LogicalName FROM @Table WHERE Type='D')
   SET @LogicalNameLog=(SELECT LogicalName FROM @Table WHERE Type='L')

SELECT @LogicalNameData,@LogicalNameLog

回答by user1079398

DECLARE @Path VARCHAR(1000)= N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\aaa.bak',
        @RestorePath NVARCHAR(max)='C:\'

DECLARE @Table TABLE
    (
      LogicalName VARCHAR(128) ,
      [PhysicalName] VARCHAR(128) ,
      [Type] VARCHAR ,
      [FileGroupName] VARCHAR(128) ,
      [Size] VARCHAR(128) ,
      [MaxSize] VARCHAR(128) ,
      [FileId] VARCHAR(128) ,
      [CreateLSN] VARCHAR(128) ,
      [DropLSN] VARCHAR(128) ,
      [UniqueId] VARCHAR(128) ,
      [ReadOnlyLSN] VARCHAR(128) ,
      [ReadWriteLSN] VARCHAR(128) ,
      [BackupSizeInBytes] VARCHAR(128) ,
      [SourceBlockSize] VARCHAR(128) ,
      [FileGroupId] VARCHAR(128) ,
      [LogGroupGUID] VARCHAR(128) ,
      [DifferentialBaseLSN] VARCHAR(128) ,
      [DifferentialBaseGUID] VARCHAR(128) ,
      [IsReadOnly] VARCHAR(128) ,
      [IsPresent] VARCHAR(128) ,
      [TDEThumbprint] VARCHAR(128)
    )

DECLARE @LogicalNameData VARCHAR(128) ,
    @LogicalNameLog VARCHAR(128)
INSERT  INTO @table
        EXEC ( '
RESTORE FILELISTONLY 
   FROM DISK=''' + @Path + '''
   '
            )

DECLARE @restoreScript NVARCHAR(max)='RESTORE DATABASE [aaa] FROM DISK =''' + @Path + ''' WITH FILE = 1 '

SELECT  @restoreScript +=CHAR(10) + ' ,MOVE  ''' +  LogicalName + ''' TO ''' + 
        @RestorePath  + LogicalName + RIGHT(PhysicalName,4) + ''''
                         FROM   @Table
                         WHERE  Type = 'D'

 SELECT  @restoreScript += ' ,MOVE  ''' +  LogicalName + ''' TO ''' + @RestorePath  + LogicalName + '.ldf'''
                        FROM    @Table
                        WHERE   Type = 'L'

SET @restoreScript += ' , NOUNLOAD, REPLACE, STATS = 10 '
SELECT  @restoreScript

回答by Timo Riikonen

If you are making your changes manually, then you can find the corresponding names with the following query:

如果您手动进行更改，则可以使用以下查询找到相应的名称：

SELECT db.name AS [DB Name], mf.name AS [Full logical name],
CASE
  WHEN CHARINDEX('_', mf.name) > 0 THEN LEFT (mf.name, CHARINDEX('_', mf.name)-1)
  ELSE mf.name
END AS [Shortened logical name]
FROM sys.master_files mf
INNER JOIN sys.databases db ON db.database_id = mf.database_id
WHERE mf.type_desc != 'LOG'
ORDER BY db.name;