How can I retrieve the logical file name of the database from backup file
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
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.
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.
From: 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):
RESTORE FILELISTONLY FROM DISK = 'D:\MyBackups\Backup.bak'