SQL Server RESTORE FILELISTONLY

Overview

The RESTORE FILELISTONLY option allows you to see a list of the files that were backed up.  So for example if you have a full backup you will see all of the data files (mdf) and the log file (ldf).

Explanation

This information can only be returned using T-SQL there is not a way to get this information from SQL Server Management Studio. Although if you do a restore and select options, you will see some of this information in SSMS.

The RESTORE FILELISTONLY option can be simply issued as follows for a backup that exists on disk.  If there are multiple backups in one file and you do not specify “WITH FILE = X” you will only get information for the first backup in the file.  To get the FILE number use RESTORE HEADERONLY and use the “Position” column.

Get filelistlonly information from a backup file

RESTORE FILELISTONLY FROM DISK = 'C:\AdventureWorks.BAK' WITH FILE = 1
GO

The result set would like the following.  The things that are helpful here include the LogicalName and PhysicalName.