RESTORE HEADERONLY and FILELISTONLY: what is in a backup
Read a SQL Server backup file with RESTORE HEADERONLY and RESTORE FILELISTONLY: what the columns mean, how to pick a backup set, and how to see the tables too.
Two commands tell you what a backup file is without restoring anything: RESTORE HEADERONLY describes the backup sets in the file, and RESTORE FILELISTONLY describes the database files in one backup set. Both read only the start of the file, so they are fast, also for a backup of terabytes. The tables below explain the columns of both result sets. They run on any SQL Server instance; the instance does not have to be the one that made the backup. On a computer without SQL Server, SQLTreeo Restore shows the backup sets and database files from the file itself, and the tables in it as well.
RESTORE HEADERONLY FROM DISK = N'D:\Backups\Sales_FULL.bak';
RESTORE FILELISTONLY FROM DISK = N'D:\Backups\Sales_FULL.bak';
RESTORE HEADERONLY: one row per backup set
A file can hold several backup sets, for example a full backup with differential and log backups appended to it by WITH NOINIT. HEADERONLY returns one row for each. The most useful columns:
| Column | What it tells you |
|---|---|
Position |
The number of the backup set in the file. Use it as WITH FILE = n in the other commands. |
BackupType and BackupTypeDescription |
1 Database, 2 Transaction log, 4 File, 5 Differential database, 6 Differential file, 7 Partial, 8 Differential partial. |
DatabaseName, ServerName, MachineName |
Which database, from which instance and computer. |
UserName |
Who ran the backup. |
BackupStartDate, BackupFinishDate |
When it started and ended. |
BackupSize, CompressedBackupSize |
Size in bytes. The two are equal when the backup is not compressed. |
Compressed |
1 when the backup is compressed. |
IsCopyOnly |
1 for a copy-only backup, which does not disturb the differential base. |
HasBackupChecksums |
1 when the backup was made WITH CHECKSUM, which RESTORE VERIFYONLY can then verify. |
IsDamaged |
1 when the backup operation found the database damaged. |
SoftwareVersionMajor, Minor, Build |
The SQL Server version that made it: major 17 is SQL Server 2025, 16 is 2022, 15 is 2019, 14 is 2017, 13 is 2016. A backup cannot be restored on an older version. |
Collation, RecoveryModel, CompatibilityLevel |
Settings of the database when it was backed up. |
FirstLSN, LastLSN, CheckpointLSN, DatabaseBackupLSN |
How backups follow each other. The DatabaseBackupLSN of a differential or log backup equals the CheckpointLSN of the full backup it builds on. |
KeyAlgorithm, EncryptorThumbprint |
Filled in for a backup made WITH ENCRYPTION: the algorithm and the certificate or key that protects it. |
To look at the second backup set of a file, add WITH FILE:
RESTORE HEADERONLY FROM DISK = N'D:\Backups\Sales_multi.bak';
RESTORE FILELISTONLY FROM DISK = N'D:\Backups\Sales_multi.bak' WITH FILE = 2;
RESTORE FILELISTONLY: the files of one backup set
| Column | What it tells you |
|---|---|
LogicalName |
The name to use in WITH MOVE when you restore to other paths. |
PhysicalName |
The path the file had on the source server. |
Type |
D data file, L log file, F full-text catalog, S FILESTREAM, FileTable or memory-optimized container. |
FileGroupName |
The filegroup of a data file. |
Size, MaxSize |
The size of the file in bytes, and its limit. This is the room a restore needs. |
BackupSizeInBytes |
How much of the file is in this backup. Unused space is not backed up, so this is often less than Size. |
IsPresent |
0 when the file is not in this backup set (a file backup or a partial backup). |
TDEThumbprint |
The thumbprint of the certificate that protects a TDE database. |
Size is why a small backup can need a large disk: the restore recreates the files at the size they had. Log files count as well.
To see more than the header
- Tables and rows. SQLTreeo Restore reads the tables straight from the file, with their row counts and sizes. Restoring the backup on an instance shows them too. The header itself holds no contents.
- Whether the backup can be restored.
RESTORE VERIFYONLYchecks that a backup is complete and readable, and with checksums that its pages are intact. An actual restore is the final proof that the data is usable. - The contents of an encrypted backup. The header of an encrypted backup can be read without the certificate; the data can be read with it. Back up the TDE certificate before you need it.