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

Question

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.

Leave a Comment Cancel reply