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.
RESTORE HEADERONLY
FROM <backup_device>
[WITH {NOUNLOAD | UNLOAD}]
<backup_device> ::=
{
{'backup_device_name' | @backup_device_name_var}
| {DISK | TAPE | PIPE} =
{'temp_backup_device' | @temp_backup_device_var}
}
Allows backups to be restored from the named disk, tape, or pipe 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:\Mssql7\Backup\Mybackup.dat’ or TAPE = \\.\TAPE0. A device type of pipe should specify the name of the named pipe that is used by the client application. If specified as a variable (@temp_backup_device_var), the device name can be specified either as a string constant (@temp_backup_device_var = ‘temp_backup_device’) or as a variable of character string data type, except for the ntext or text data types.
Pipe devices have been added to allow third-party vendors a flexible way to connect their own software. The pipe device is not used for typical Transact-SQL use.
If you are using either a network server with a UNC name or a redirected drive letter, specify a device type of disk.
RESTORE permissions default to members of the sysadmin fixed server role and the db_owner fixed database role, and are not transferable.
A client can use RESTORE HEADERONLY to retrieve all the backup header information (subject to having the necessary permissions) 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 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.
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 |
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 = Temporary 102 = Permanent Tape 5 = Temporary 105 = Permanent Pipe 6 = Temporary 106 = Permanent Virtual Device 7 = Temporary 107 = Permanent All permanent 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. | 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. | NULL |
| UnicodeComparisonStyle | int | Server Unicode comparison style configuration option, which provides additional control over the sorting of Unicode data. | 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. |
Note If the backup set is password protected, the BackupName column value is ***Password Protected***, and all other columns are NULL. For any backup, only the appropriate columns contain a value; all other columns are NULL.
Important To maintain backward compatibility, the LOAD keyword can be used in place of the RESTORE keyword in the RESTORE statement syntax.
| BACKUP | RESTORE |
| RESTORE FILELISTONLY | RESTORE VERIFYONLY |
| RESTORE LABELONLY | Backing Up and Restoring Databases |
| Understanding Media Sets and Families | Using Identifiers |
| Data Types |