Reading a Backup File Before You Restore With RESTORE HEADERONLY

A backup file arrives with a reassuring name and no useful explanation. RESTORE HEADERONLY tells you which backup sets it contains before you choose a restore.

Read the File Without Restoring a Database

A file extension cannot identify a database, backup type, or source version. The same file can hold several backup sets. Its name can describe yesterday's contents while today's backup was appended to it.

I inspect the header before discussing a target database name. That check catches mistaken files before a restore consumes time and storage. A promising filename has never been a recovery plan.

Use an account with permission to read backup metadata. The SQL Server service also needs access to the media path. A path visible from your desktop is not automatically visible from the server.

RESTORE HEADERONLY
FROM DISK = N'C:\SqlBackups\Incoming.bak';
RESTORE LABELONLY
FROM DISK = N'C:\SqlBackups\Incoming.bak';

Replace the sample path with a file on the server or an approved share. These commands inspect metadata. They do not overwrite a database or prove that every page inside the backup is readable.

HEADERONLY returns one row per available backup set. LABELONLY describes the media set and its media family. Read both when you need to understand whether the supplied file is one part of a larger backup.

An access error is a separate problem from a damaged backup. Check the path, service identity, and file permissions first. Repeatedly changing restore options will not grant Windows access to a folder.

Read the Database and Backup Type With RESTORE HEADERONLY

Start with DatabaseName and BackupType. A database backup, transaction log backup, and differential backup serve different roles. Their filenames do not enforce those roles, so use the returned type.

BackupType uses documented numeric values. Type 1 identifies a database backup, type 2 a transaction log backup, and type 5 a differential database backup. File and partial backup types have their own values.

Also inspect BackupStartDate and BackupFinishDate. Those values describe the backup operation according to the source server's clock. Compare time zone assumptions before placing files in chronological order across different servers.

A later finish date does not make every backup an independent starting point. A differential depends on its base. A log backup belongs to a log sequence. Inspect the LSN and recovery-fork information when assembling that sequence.

Check IsCopyOnly when the distinction matters. A copy-only full backup avoids changing the differential base. It still contains a full database backup suitable for an appropriate restore sequence.

Write down the source database identity alongside the chosen set. DatabaseName is useful, but identical names exist on different instances. A restore decision needs the intended source, not only a familiar name.

Separate the Source Version From Compression

SoftwareVersionMajor identifies the SQL Server major version that produced the backup. DatabaseVersion represents the database's internal version. They are different values and should not be read as interchangeable product labels.

SQL Server cannot restore a database backup created by a newer engine onto an older engine. Check the destination engine before allocating a recovery window. Raising database compatibility level does not solve that restriction.

SELECT SERVERPROPERTY('ProductVersion') AS DestinationVersion,
       SERVERPROPERTY('ProductMajorVersion') AS DestinationMajorVersion,
       SERVERPROPERTY('Edition') AS DestinationEdition;

Compare the destination result with the header row you selected. Also review edition-dependent features and the supported upgrade path. A sufficiently new engine version does not settle every migration requirement.

The HEADERONLY result column for software compression is named Compressed. Some descriptions call it IsCompressed, but that is not the column returned by this command. Use the actual heading in your result grid. On my SQL Server 2025 instance, a CompressionAlgorithm column sat beside it and showed MS_XPRESS for a compressed test backup.

Compression changes the stored representation, not the need for verification. A compressed file can still be unreadable, incomplete, or missing another media family. Encryption creates additional certificate and key requirements that need separate preparation.

Do not infer database allocation from the compressed file's size. Read FILELISTONLY for logical files and their sizes. The destination requires room for restored files, not only room for the incoming backup file.

RESTORE HEADERONLY Shows Multiple Sets in One File

Backup operations can append another set to existing media. HEADERONLY then returns multiple rows with different Position values. Selecting the newest filename therefore does not select the newest backup set inside it.

This disposable demonstration writes two copy-only full backups into the same new file. Use a dedicated test database and a new path. INIT replaces existing backup sets in the specified media file.

BACKUP DATABASE [BackupHeaderDemo]
TO DISK = N'C:\SqlBackups\HeaderSetsDemo.bak'
WITH INIT, COPY_ONLY, CHECKSUM, NAME = N'First test set';

BACKUP DATABASE [BackupHeaderDemo]
TO DISK = N'C:\SqlBackups\HeaderSetsDemo.bak'
WITH NOINIT, COPY_ONLY, CHECKSUM, NAME = N'Second test set';

RESTORE HEADERONLY
FROM DISK = N'C:\SqlBackups\HeaderSetsDemo.bak';

Read Position and Name together. The first set and the appended set occupy separate positions. The position belongs to this media file, so do not reuse it blindly after someone supplies a replacement file.

For a striped backup, obtain every required media family. LABELONLY helps identify the media set and family sequence. One stripe is not a smaller self-contained copy of the whole database.

I keep the selected position with the file identity during restore preparation. Otherwise, a later operator must repeat the interpretation from scratch. A filename and the phrase latest backup leave too much room for guessing.

Select the Intended Position Explicitly

FILE chooses the backup-set position. Use the same selection when checking logical files, verifying the backup, and restoring it. A mismatch across these steps defeats the inspection you performed first.

RESTORE FILELISTONLY
FROM DISK = N'C:\SqlBackups\HeaderSetsDemo.bak'
WITH FILE = 2;

RESTORE VERIFYONLY
FROM DISK = N'C:\SqlBackups\HeaderSetsDemo.bak'
WITH FILE = 2, CHECKSUM;

The number here belongs to the demonstration's appended set. Replace it with the position you actually read. Where a media file contains unrelated databases, selecting the wrong row can produce a convincing but irrelevant result.

A restore using the chosen set starts with RESTORE DATABASE and the same FILE option. Add MOVE for every logical file when restoring to another location. Use a fresh target and verify that its physical paths do not conflict.

-- Adapt logical names and unused paths from FILELISTONLY first.
RESTORE DATABASE [BackupHeaderDemoRestored]
FROM DISK = N'C:\SqlBackups\HeaderSetsDemo.bak'
WITH FILE = 2,
     MOVE N'BackupHeaderDemo' TO N'C:\SqlData\BackupHeaderDemoRestored.mdf',
     MOVE N'BackupHeaderDemo_log' TO N'C:\SqlData\BackupHeaderDemoRestored_log.ldf',
     RECOVERY, STATS = 10;

Never treat those sample logical names as values discovered from your file. FILELISTONLY supplies them. Confirm the destination directories, permissions, and free space before executing the adapted restore.

Finish With a Test You Can Trust

What will you rely on if the header looks healthy but the restore fails? Header inspection answers identification questions. It does not establish the condition of every stored page or the completeness of your recovery chain.

VERIFYONLY checks that the backup set is complete and readable without restoring the database. It is useful, but it does not replace every consistency check. A successful test restore followed by appropriate database checks gives stronger recovery evidence.

Keep the inspection output, selected position, verification result, and test-restore result together. Include the instance and collection time. That record makes a future recovery discussion concrete without turning metadata into a guarantee.

RESTORE HEADERONLY identifies the backup sets before a restore changes anything. Keep the RESTORE HEADERONLY output with the reviewed FILE position so later tests use the same set.

Related reading on this blog: Identify Version of SQL Server from Backup File and Automated Restore Tests: Proving Backups Work Every Week.

A readable backup header is not proof of recovery, it is the information needed to choose the right test.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.