By: Greg Robidoux
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
T-SQL
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.
| 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 |
