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

Question

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

Database YourDB has 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 LogicalName column 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' 

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

Answer 1

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.