RESTORE HEADERONLY (Transact-SQL)
Returns a result set containing all the backup header information for all backup sets on a particular backup device.
Note
For the descriptions of the arguments, see RESTORE Arguments (Transact-SQL).
Transact-SQL Syntax Conventions
Syntax
RESTORE HEADERONLY
FROM <backup_device>
[ WITH
[ { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
[ [ , ] FILE = backup_set_file_number ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword |
@mediapassword_variable } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] REWIND ]
[ [ , ] { UNLOAD | NOUNLOAD } ]
]
[;]
<backup_device> ::=
{
{ logical_backup_device_name |
@logical_backup_device_name_var }
| { DISK | TAPE } = { 'physical_backup_device_name' |
@physical_backup_device_name_var }
}
Arguments
For descriptions of the RESTORE HEADERONLY arguments, see RESTORE Arguments (Transact-SQL).
Result Sets
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 the 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 Microsoft SQL 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
Description for other backup sets
BackupName
nvarchar(128)
Backup set name.
Data set name
BackupDescription
nvarchar(255)
Backup set description.
Data set description
BackupType
smallint
Backup type:
1 = Database
2 = Transaction log
4 = File
5 = Differential database
6 = Differential file
7 = Partial
8 = Differential partial
Backup type:
1 = Normal
5 = Differential
16 = Incremental
17 = Daily
ExpirationDate
datetime
Expiration date for the backup set.
NULL
Compressed
tinyint
0 = No. SQL Server does not support software compression.
Whether the backup set is compressed using software-based compression:
1 = Yes
0 = No
Position
smallint
Position of the backup set in the volume (for use with the FILE = option).
Position of the backup set in the volume
DeviceType
tinyint
Number corresponding to the device used for the backup operation.
Disk:
2 = Logical
102 = Physical
Tape:
5 = Logical
105 = Physical
Virtual Device:
7 = Logical
107 = Physical
Logical device names and device numbers are in sys.backup_devices; for more information, see sys.backup_devices (Transact-SQL).
NULL
UserName
nvarchar(128)
User name that performed the backup operation.
User name that performed the backup operation
ServerName
nvarchar(128)
Name of the server that wrote the backup set.
NULL
DatabaseName
nvarchar(128)
Name of the database that was backed up.
NULL
DatabaseVersion
int
Version of the database from which the backup was created.
NULL
DatabaseCreationDate
datetime
Date and time the database was created.
NULL
BackupSize
numeric(20,0)
Size of the backup, in bytes.
NULL
FirstLSN
numeric(25,0)
Log sequence number of the first log record in the backup set.
NULL
LastLSN
numeric(25,0)
Log sequence number of the next log record after the backup set.
NULL
CheckpointLSN
numeric(25,0)
Log sequence number of the most recent checkpoint at the time the backup was created.
NULL
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.
NULL
BackupStartDate
datetime
Date and time that the backup operation began.
Media Write Date
BackupFinishDate
datetime
Date and time that the backup operation finished.
Media Write Date
SortOrder
smallint
Server sort order. This column is valid for database backups only. Provided for backward compatibility.
NULL
CodePage
smallint
Server code page or character set used by the server.
NULL
UnicodeLocaleId
int
Server Unicode locale ID configuration option used for Unicode character data sorting. Provided for backward compatibility.
NULL
UnicodeComparisonStyle
int
Server Unicode comparison style configuration option, which provides additional control over the sorting of Unicode data. Provided for backward compatibility.
NULL
CompatibilityLevel
tinyint
Compatibility level setting of the database from which the backup was created.
NULL
SoftwareVendorId
int
Software vendor identification number. For SQL Server, this number is 4608 (or hexadecimal 0x1200).
Software vendor identification number
SoftwareVersionMajor
int
Major version number of the server that created the backup set.
Major version number of the software that created the backup set
SoftwareVersionMinor
int
Minor version number of the server that created the backup set.
Minor version number of the software that created the backup set
SoftwareVersionBuild
int
Build number of the server that created the backup set.
NULL
MachineName
nvarchar(128)
Name of the computer that performed the backup operation.
Type of the computer that performed the backup operation
Flags
int
Individual flags bit meanings if set to 1:
1 = Log backup contains bulk-logged operations.
2 = Snapshot backup.
4 = Database was read-only when backed up.
8 = Database was in single-user mode when backed up.
16 = Backup contains backup checksums.
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).
NULL
BindingID
uniqueidentifier
Binding ID for the database. This corresponds to sys.databasesdatabase_guid. When a database is restored, a new value is assigned. Also see FamilyGUID (below).
NULL
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.
NULL
Collation
nvarchar(128)
Collation used by the database.
NULL
FamilyGUID
uniqueidentifier
ID of the original database when created. This value stays the same when the database is restored.
NULL
HasBulkLoggedData
bit
1 = Log backup containing bulk-logged operations.
NULL
IsSnapshot
bit
1 = Snapshot backup.
NULL
IsReadOnly
bit
1 = Database was read-only when backed up.
NULL
IsSingleUser
bit
1 = Database was single-user when backed up.
NULL
HasBackupChecksums
bit
1 = Backup contains backup checksums.
NULL
IsDamaged
bit
1 = Database was damaged when backed up, but the backup operation was requested to continue despite errors.
NULL
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.
NULL
HasIncompleteMetaData
bit
1 = A tail-log backup with incomplete meta-data.
For information about tail-log backups with incomplete backup metadata, see Tail-Log Backups.
NULL
IsForceOffline
bit
1 = Backup taken with NORECOVERY; the database was taken offline by backup.
NULL
IsCopyOnly
bit
1 = A copy-only backup.
A copy-only backup does not impact the overall backup and restore procedures for the database. For more information, see Copy-Only Backups.
NULL
FirstRecoveryForkID
uniqueidentifier
ID for the starting recovery fork. This column corresponds to first_recovery_fork_guid in the backupset table.
For data backups, FirstRecoveryForkID equals RecoveryForkID.
NULL
ForkPointLSN
numeric(25,0) NULL
If FirstRecoveryForkID is not equal to RecoveryForkID, this is the log sequence number of the fork point. Otherwise, this value is NULL.
NULL
RecoveryModel
nvarchar(60)
Recovery model for the Database, one of:
FULL
BULK-LOGGED
SIMPLE
NULL
DifferentialBaseLSN
numeric(25,0) NULL
For a single-based differential backup, the value equals the FirstLSN of the differential base; changes with LSNs greater than or equal to DifferentialBaseLSN are included in the differential.
For a multi-based differential, the value is NULL, and the base LSN must be determined at the file level. For more information, see RESTORE FILELISTONLY (Transact-SQL).
For non-differential backup types, the value is always NULL.
For more information, see Base of a Differential Backup.
NULL
DifferentialBaseGUID
uniqueidentifier
For a single-based differential backup, the value is the unique identifier of the differential base.
For multi-based differentials, the value is NULL, and the differential base must be determined per file.
For non-differential backup types, the value is NULL.
NULL
BackupTypeDescription
nvarchar(60)
Backup type as string, one of:
DATABASE
TRANSACTION LOG
FILE OR FILEGROUP
DATABASE DIFFERENTIAL
FILE DIFFERENTIAL PARTIAL
PARTIAL DIFFERENTIAL
Backup type as string, one of:
NORMAL
DIFFERENTIAL
INCREMENTAL
DAILY
BackupSetGUID
uniqueidentifier NULL
Unique identification number of the backup set, by which it is identified on the media.
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.
Remarks
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.
Permissions
Any user may use RESTORE HEADERONLY.
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 2005 tools. However, a password does not prevent overwrite of media using the BACKUP statement's FORMAT option.
Security Note: |
---|
The protection provided by this password is weak. It is intended to prevent an incorrect restore using SQL Server 2005 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. 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. |
Examples
The following example returns the information in the header for the disk file C:\AdventureWorks-FullBackup.bak
.
RESTORE HEADERONLY
FROM DISK = N'C:\AdventureWorks-FullBackup.bak'
WITH NOUNLOAD;
GO
See Also
Reference
BACKUP (Transact-SQL)
backupset (Transact-SQL)
RESTORE REWINDONLY (Transact-SQL)
RESTORE VERIFYONLY (Transact-SQL)
RESTORE (Transact-SQL)
Other Resources
Viewing Information About Backups
Base of a Differential Backup
How to: Enable or Disable Backup Checksums (Transact-SQL)
Introduction to Log Sequence Numbers
Log Sequence Numbers and Restore Planning
Media Sets, Media Families, and Backup Sets
Overview of the Recovery Models
Recovery Paths