RESTORE HEADERONLY and FILELISTONLY: what is in a backup

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. They run on any SQL Server instance; the instance does not have to be the one that made the backup.

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.

What these commands cannot tell you

• Tables and rows. The header knows nothing about the contents. To see which tables a backup holds, with their row counts and sizes, restore it on an instance or open it with SQLTreeo Restore, which reads the tables straight from the file.
• Whether the backup can be restored. RESTORE VERIFYONLY checks that a backup is complete and readable, and with checksums that its pages are intact. Only an actual restore proves the data is usable.
• The contents of an encrypted backup. The header of an encrypted backup can be read without the certificate; the data cannot. Back up the TDE certificate before you need it.

Related: How to open a .bak file without SQL Server.