RESTORE HEADERONLY
Retrieves all the backup header information for all backup sets on a particular backup device. The result from executing RESTORE HEADERONLY is a result set.
Syntax
RESTORE HEADERONLY
FROM < backup_device >
[ WITH { NOUNLOAD | UNLOAD }
[ [ , ] FILE = file_number ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
]
< backup_device > ::=
{
{ 'logical_backup_device_name' | @logical_backup_device_name_var }
| { DISK | TAPE } =
{ 'physical_backup_device_name' | @physical_backup_device_name_var }
}
Arguments
<backup_device>
Specifies the logical or physical backup device(s) to use for the restore. Can be one of the following:
- {'logical_backup_device_name' | @logical_backup_device_name_var}
- Is the logical name, which must follow the rules for identifiers, of the backup device created by sp_addumpdevice from which the database is restored. If supplied as a variable (@logical_backup_device_name_var), the backup device name can be specified either as a string constant (@logical_backup_device_name_var = 'logical_backup_device_name') or as a variable of character string data type, except for the ntext or text data types.
- {DISK | TAPE } =
- 'physical_backup_device_name' | @physical_backup_device_name_var
Allows backups to be restored from the named disk or tape device. The device types of disk and tape should be specified with the actual name (for example, complete path and file name) of the device: DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Mybackup.dat' or TAPE = '\\.\TAPE0'. If specified as a variable (@physical_backup_device_name_var), the device name can be specified either as a string constant (@physical_backup_device_name_var = 'physical_backup_device_name') or as a variable of character string data type, except for the ntext or text data types.
If using either a network server with a UNC name or a redirected drive letter, specify a device type of disk.
NOUNLOAD
Specifies that the tape is not unloaded automatically from the tape drive after a restore. NOUNLOAD remains set until UNLOAD is specified. This option is used only for tape devices.
UNLOAD
Specifies that the tape is automatically rewound and unloaded when the restore is finished. UNLOAD is set by default when a new user session is started. It remains set until NOUNLOAD is specified. This option is used only for tape devices.
FILE = file_number
Identifies the backup set to be described. For example, a file_number of 1 indicates the first backup set and a file_number of 2 indicates the second backup set. If not specified, all sets on the device are described.
PASSWORD = { password | @password_variable}
Is the password for the backup set. PASSWORD is a character string. If a password was provided when the backup set was created, the password must be supplied to perform any restore operation from the backup set.
For more information about using passwords, see Permissions.
MEDIAPASSWORD = { mediapassword | @mediapassword_variable}
Is the password for the media set. MEDIAPASSWORD is a character string.
If a password was provided when the media set was formatted, that password must be supplied to create a backup set on that media set. In addition, that media password also must be supplied to perform any restore operation from the media set.
Result Sets
For each backup on a given device, the server sends a row of header information with the following columns:
Note Because RESTORE HEADERONLY looks at all backup sets on the media, it can take some time to produce this result set when using high-capacity tape drives. To get a quick look at the media without getting information about every backup set, use RESTORE LABELONLY or specify the FILE = file_number.
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™ 2000 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 |
Backup type:
1 = Normal |
| 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 |
| 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 Tape Pipe Virtual Device All physical device names and device numbers can be found in sysdevices. |
NULL |
| UserName | nvarchar(128) | Username that performed the backup operation. | Username 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 transaction in the backup set. NULL for file backups. | NULL |
| LastLSN | numeric(25,0) | Log sequence number of the last transaction in the backup set. NULL for file backups. | 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. | 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 | Bit 0 (X1) indicates bulk-logged data is captured in this log backup. | NULL |
| BindingID | uniqueidentifier | Binding ID for the database. | NULL |
| RecoveryForkID | uniqueidentifier | ID for the current recovery fork for this backup. | NULL |
| Collation | nvarchar(128) | Collation used by the database. | NULL |
Note If passwords are defined for the backup sets, RESTORE HEADERONLY will show complete information for only the backup set whose password matches the specified PASSWORD option of the command. RESTORE HEADERONLY also will show complete information for unprotected backup sets. The BackupName column for the other password-protected backup sets on the media will be set to '***Password Protected***', and all other columns will be NULL.
Permissions
Any user may use RESTORE HEADERONLY.
In addition, the user may specify passwords for a media set, a backup set, or both. When a password is defined on a media set, it is not enough that a user is a member of appropriate fixed server and database roles to perform a backup. The user also must supply the media password to perform these operations. Similarly, restore is not allowed unless the correct media password and backup set password are specified in the restore command.
Defining passwords for backup sets and media sets is an optional feature in the BACKUP statement. The passwords will prevent unauthorized restore operations and unauthorized appends of backup sets to media using SQL Server 2000 tools, but passwords do not prevent overwrite of media with the FORMAT option.
Thus, although the use of passwords can help protect the contents of media from unauthorized access using SQL Server tools, passwords do not protect contents from being destroyed. Passwords do not fully prevent unauthorized access to the contents of the media because the data in the backup sets is not encrypted and could theoretically be examined by programs specifically created for this purpose. For situations where security is crucial, it is important to prevent access to the media by unauthorized individuals.
It is an error to specify a password if none is defined.
Remarks
A client can use RESTORE HEADERONLY to retrieve all the backup header information for all backups on a particular backup device. The header information is sent as a row by the server for each backup on a given backup device in a table.
Important To maintain backward compatibility, the LOAD keyword can be used in place of the RESTORE keyword in the RESTORE statement syntax.
See Also
Backing Up and Restoring Databases