The RESTORE SQBHEADERONLY command
Published 13 February 2013
Use the RESTORE SQBHEADERONLY
command with the SQL Backup Pro -SQL
parameter to retrieve the header information associated with a SQL Backup backup file using the command line or extended stored procedure.
For compressed backup files, using RESTORE SQBHEADERONLY
to view header information is much quicker than using the equivalent SQL Server command, which necessarily requires that the entire backup file is uncompressed.
When using the extended stored procedure the parameter or set of parameters (such as -SQL
) must be delimited by single quotes. Therefore, wherever a single quote is used for the arguments below, for the extended stored procedure you must use two single quotes so that SQL Server does not interpret it as a string delimiter. See Using the extended stored procedure for more information.
Syntax
Restore a backup header
RESTORE SQBHEADERONLY
[ FROM { DISK } = { 'physical_backup_device_name' } ]
[ WITH
[ [ , ] PASSWORD = { 'password' | 'FILE:file_path'} ]
[ [ , ] SINGLERESULTSET ]
]
You can use wildcard characters (*) for the location of the backups (physical_backup_device_name) to display the headers of multiple files.
WITH options
PASSWORD
Specifies the password to be used with encrypted backup files. If you are restoring multiple headers at the same time, the encrypted files must all have the same password.
Storing your password in a plain text file (only available in SQL Backup 7.5 and later)
If you don't want the password to be stored in your SQL Agent jobs, you can use a password stored in a plain text file instead. This means access to the password can be restricted using Windows file permissions.
To do this, specify the file path and name after the PASSWORD keyword instead of the password itself.
Example
PASSWORD = 'FILE:C:\mypasswords\password.txt'
SQL Backup will read only the first line of text in the file (up to the first line return), and ignore everything after.
SINGLERESULTSET
Specifies that the results returned by the RESTORE SQBHEADERONLY
command should be limited to just one result set. This may be useful if you want to manipulate results using a Transact-SQL script. Such scripts can only manipulate results when a single result set is returned. The RESTORE SQBHEADERONLY
command will return two result sets by default unless you specify the SINGLERESULTSET
keyword.
Examples
Restoring a header file
This example retrieves the header information for the pubs.sqb database backup file.
SQLBackupC.exe -I {instance name} -SQL "RESTORE SQBHEADERONLY FROM DISK = 'C:\Backups\pubs.sqb'"
EXECUTE master..sqlbackup '-SQL "RESTORE SQBHEADERONLY FROM DISK = ''C:\Backups\pubs.sqb''"'
Restoring multiple header files
This example retrieves the header information for all database backup files in the Backups folder.
SQLBackupC.exe -I {instance name} -SQL "RESTORE SQBHEADERONLY FROM DISK = 'C:\Backups*.sqb'"
EXECUTE master..sqlbackup '-SQL "RESTORE SQBHEADERONLY FROM DISK = ''C:\Backups*.sqb''"'