For each backup on a given device, the server sends a row of header information with the following columns:

Note

RESTORE HEADERONLY looks at all backup sets on the media. Therefore, producing this result set when using high-capacity tape drives can take some time. To get a quick look at the media without getting information about every backup set, use RESTORE LABELONLY or specify FILE =backup_set_file_number.

Note

Due to the nature of Microsoft Tape Format, it is possible for backup sets from other software programs to occupy space on the same media as MicrosoftSQL Server backup sets. The result set returned by RESTORE HEADERONLY includes a row for each of these other backup sets.

Column name

Data type

Description for SQL Server backup sets

BackupName

nvarchar(128)

Backup set name.

BackupDescription

nvarchar(255)

Backup set description.

BackupType

smallint

Backup type:

1 = Database

2 = Transaction log

4 = File

5 = Differential database

6 = Differential file

7 = Partial

8 = Differential partial

ExpirationDate

datetime

Expiration date for the backup set.

Compressed

BYTE(1)

Whether the backup set is compressed using software-based compression:

0 = No

1 = Yes

Position

smallint

Position of the backup set in the volume (for use with the FILE = option).

Log sequence number of the most recent checkpoint at the time the backup was created.

DatabaseBackupLSN

numeric(25,0)

Log sequence number of the most recent full database backup.

DatabaseBackupLSN is the “begin of checkpoint” that is triggered when the backup starts. This LSN will coincide with FirstLSN if the backup is taken when the database is idle and no replication is configured.

32 = Database was damaged when backed up, but the backup operation was requested to continue despite errors.

64 = Tail log backup.

128 = Tail log backup with incomplete metadata.

256 = Tail log backup with NORECOVERY.

Important: We recommend that instead of Flags you use the individual Boolean columns (listed below starting with HasBulkLoggedData and ending with IsCopyOnly).

BindingID

uniqueidentifier

Binding ID for the database. This corresponds to sys.database_recovery_status****database_guid. When a database is restored, a new value is assigned. Also see FamilyGUID (below).

RecoveryForkID

uniqueidentifier

ID for the ending recovery fork. This column corresponds to last_recovery_fork_guid in the backupset table.

For data backups, RecoveryForkID equals FirstRecoveryForkID.

Collation

nvarchar(128)

Collation used by the database.

FamilyGUID

uniqueidentifier

ID of the original database when created. This value stays the same when the database is restored.

HasBulkLoggedData

bit

1 = Log backup containing bulk-logged operations.

IsSnapshot

bit

1 = Snapshot backup.

IsReadOnly

bit

1 = Database was read-only when backed up.

IsSingleUser

bit

1 = Database was single-user when backed up.

HasBackupChecksums

bit

1 = Backup contains backup checksums.

IsDamaged

bit

1 = Database was damaged when backed up, but the backup operation was requested to continue despite errors.

BeginsLogChain

bit

1 = This is the first in a continuous chain of log backups. A log chain begins with the first log backup taken after the database is created or when it is switched from the Simple to the Full or Bulk-Logged Recovery Model.

The type of encryptor used: Certificate or Asymmetric Key. When the backup was not encrypted, this value is NULL.

Note

If passwords are defined for the backup sets, RESTORE HEADERONLY shows complete information for only the backup set whose password matches the specified PASSWORD option of the command. RESTORE HEADERONLY also shows complete information for unprotected backup sets. The BackupName column for the other password-protected backup sets on the media is set to '***Password Protected***', and all other columns are NULL.

A client can use RESTORE HEADERONLY to retrieve all the backup header information for all backups on a particular backup device. For each backup on the backup device, the server sends the header information as a row.

A backup operation may optionally specify passwords for a media set, a backup set, or both. When a password has been defined on a media set or backup set, you must specify the correct password or passwords in the RESTORE statement. These passwords prevent unauthorized restore operations and unauthorized appends of backup sets to media using Microsoft SQL Server tools. However, a password does not prevent overwrite of media using the BACKUP statement's FORMAT option.

Important

The protection provided by this password is weak. It is intended to prevent an incorrect restore using SQL Server tools by authorized or unauthorized users. It does not prevent the reading of the backup data by other means or the replacement of the password. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.The best practice for protecting backups is to store backup tapes in a secure location or back up to disk files that are protected by adequate access control lists (ACLs). The ACLs should be set on the directory root under which backups are created.