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
GOThe result set would like the following. The things that are helpful here include the LogicalName and PhysicalName.
| ColumnName | Value – Row 1 |
| LogicalName | AdventureWorks_Data |
| PhysicalName | C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf |
| Type | D |
| FileGroupName | PRIMARY |
| Size | 202113024 |
| MaxSize | 35184372080640 |
| FileId | 1 |
| CreateLSN | 0 |
| DropLSN | 0 |
| UniqueId | 50A534B0-156C-42B7-82FE-A57D21A53EEA |
| ReadOnlyLSN | 0 |
| ReadWriteLSN | 0 |
| BackupSizeInBytes | 177012736 |
| SourceBlockSize | 512 |
| FileGroupId | 1 |
| LogGroupGUID | NULL |
| DifferentialBaseLSN | 0 |
| DifferentialBaseGUID | 00000000-0000-0000-0000-000000000000 |
| IsReadOnly | 0 |
| IsPresent | 1 |
| ColumnName | Value – Row2 |
| LogicalName | AdventureWorks_Log |
| PhysicalName | C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Log.ldf |
| Type | L |
| FileGroupName | NULL |
| Size | 153092096 |
| MaxSize | 2199023255552 |
| FileId | 2 |
| CreateLSN | 0 |
| DropLSN | 0 |
| UniqueId | 4F544777-6DBB-4BBC-818A-72C0B878610C |
| ReadOnlyLSN | 0 |
| ReadWriteLSN | 0 |
| BackupSizeInBytes | 0 |
| SourceBlockSize | 512 |
| FileGroupId | 0 |
| LogGroupGUID | NULL |
| DifferentialBaseLSN | 0 |
| DifferentialBaseGUID | 00000000-0000-0000-0000-000000000000 |
| IsReadOnly | 0 |
| IsPresent | 1 |
Greg Robidoux has been working with databases for 35+ years with extensive hands on SQL Server experience from version 6.5 to 2025. He has authored over 250 technical articles and delivered several presentations online and at various conventions. Greg is also the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server.
