LiteSpeed for SQL Server 8.9 - Configure Log Shipping Guide

xp_restore_headeronly

Retrieves the backup header information for all LiteSpeed backups. The header information is sent as a row by the server for each backup on a given backup device in a table.

Tip: To retrieve information from TSM backups, also use xp_view_tsmcontents.

This topic covers:

• Syntax

• Arguments

• Examples

• Results

• Returns

Syntax

xp_restore_headeronly (Disk)

EXEC master.dbo.xp_restore_headeronly
[@filename = 'backup_file_name'] [,...n]
[, @filenumber = n]
[, @headerdetails = 'option']
[, @attachedfiles = 0 | 1 | 2 | 3 ]

xp_restore_headeronly (Tape)

EXEC master.dbo.xp_restore_headeronly
@filename = 'tape_device_name'
[, @filenumber = n]
[, @headerdetails = 'option']
[, @attachedfiles = 0 | 1 | 2 | 3 ]

xp_restore_headeronly (TSM)

EXEC master.dbo.xp_restore_headeronly
@tsmobject = 'TSM_object'
, @tsmconfigfile = 'TSM_configuration_file'
[, @tsmclientnode = 'clientnode_name'] 
[, @tsmclientownerpwd = '****'] 
[, @tsmarchive = 0 | 1 ]
[, @tsmpointintime = 'date_time']
[, @attachedfiles = 0 | 1 | 2 | 3 ]
[, @headerdetails = 'option']

xp_restore_headeronly (Amazon S3)

EXEC master.dbo.xp_restore_headeronly
@CloudVendor = N'AmazonS3'
, @FileName = N'AA_3_1.bak'
[, @CloudBucketName = N'aabucket7']
[, @CloudAccessKey = N'***']
[, @CloudSecretKey = ***']
[, @CloudRegionName = N'us-west-2']
[, @ProxyHost = N'proxy.sitelocal']
[, @ProxyPort = 8080]
[, @ProxyLogin = N'DOMAIN\xyz-tst-MYTester']
[, @ProxyPassword = N'***']

xp_restore_headeronly (Microsoft Azure)

EXEC master.dbo.xp_restore_headeronly

@filename = N'test\test.bak',

@CloudVendor = N'AzureBlob',

@CloudAccessKeyEnc = N'*******',

@CloudSecretKeyEnc = N'******',

@CloudBucketName = N'test',

@UseSSL = 1,

@logging = 0

xp_restore_headeronly (Google Cloud Storage)

exec xp_restore_headeronly
@FileName = N'path\to\backup'
, @FileNumber = 1
, @CloudVendor = N'GoogleStorage'
, @CloudBucketName = N'bucketname'
, @CloudAccessKey = N'***' -- my key'
, @CloudSecretKey = N'***' -- my key'

Arguments

Tips:

• To see the list of accepted arguments and data types for arguments, execute the following:
  exec master.dbo.<procedure_name> show_help

• To convert the script for use with the command-line utilities, execute the following:
  exec master.dbo.<procedure_name> show_cmd, <xp_arguments>

@attachedfiles

Lists files attached to a backup. This argument accepts one of the following values:

• 0—Backup header information only
• 1—Attached files only
• 2—Backup header information and a list of attached files
• 3—Attached directories and individual files outside of those directories

@AWSAccessKey

The @AWSAccessKey argument specifies the name of the unique Amazon Web Service alphanumeric access key that identifies each user.

@AWSAccessKeyEnc

The @AWSAccessKeyEnc argument specifies the name of the encrypted unique Amazon Web Service alphanumeric access key that identifies each user.

@AWSBucketName

The @AWSBucketName argument specifies the name of the container for AWS objects. Bucket names must be at least 3 and no more than 63 characters long.

@AWSRegionName

The @AWSRegionName argument specifies the name of the Amazon Web Service region to use for a bucket. Example values are but not limited to: us-east-1, us-west-2, us-west-1, eu-west-1, ap-southeast-1, ap-southeast-2, ap-northeast-1, and sa-east-1.

@AWSSecretKey

The @AWSSecretKey argument specifies the name of the Amazon Web Service secret key that is assigned when you initially get an AWS account.

@AWSSecretKeyEnc

The @AWSSecretKeyEnc argument specifies the name of the encrypted Amazon Web Service secret key that is assigned when you initially get an AWS account.

@AWSUseGovCloud

The @AWSUseGovCloud argument enables a special restricted region for the US Government use in Amazon S3. This argument accepts one of the following values:

• 0—Do not use government cloud
• 1—Use government cloud

@AWSUseReducedRedundancy

The @AWSUseReducedRedundancy argument specifies the use of reduced redundancy storage in Amazon S3. This argument accepts one of the following values:

• 0—Do not use reduced redundancy storage
• 1—Use reduced redundancy storage

@AWSUseServerSideEncryption

The @AWSUseServerSideEncryption argument enables the encryption of data stored at rest in Amazon S3. This argument accepts one of the following values:

• 0—Do not use Server Side Encryption
• 1—Use Server Side Encryption

@CloudAccessKey

The @CloudAccessKey argument specifies the name of the unique Cloud Web Service alphanumeric access key that identifies each user. The selections include Amazon Access Key, Azure Account Name, Google e-mail styled account.

@CloudAccessKeyEnc

The @CloudAccessKeyEnc argument specifies the name of the encrypted unique Cloud Web Service alphanumeric access key that identifies each user.

@CloudBucketName

The @CloudBucketName argument specifies the name of the container for cloud objects. Bucket names must be at least 3 and no more than 63 characters long. The selections are Amazon Bucket Name, Azure Container Name, Google Bucket Name. Google Bucket Name requirements are described at https://cloud.google.com/storage/docs/naming.

@CloudGovRegion

The @CloudGovRegion argument enables a special restricted region for the US Government use in Amazon S3 and Azure Clouds. This argument accepts one of the following values:

• 0—Do not use government cloud (default)
• 1—Use government cloud

@CloudRegionName

The @CloudRegionName argument specifies the name of the Cloud Web Service region to use for a bucket. Example values are but not limited to: us-east-1, us-east-2, us-west-1, us-west-2, ca-central-1, eu-central-1, eu-west-1, eu-west-2, ap-south-1, ap-southeast-1, ap-southeast-2, ap-northeast-1, ap-northeast-2, sa-east-1, N'Germany' and N'China'.

@CloudSecretKey

The @CloudSecretKey argument specifies the name of the Cloud Web Service secret key that is assigned when you initially get a Cloud account.

@CloudSecretKeyEnc

The @CloudSecretKeyEnc argument specifies the name of the encrypted Cloud Web Service secret key that is assigned when you initially get a Cloud account.

@CloudVendor

The @CloudVendor argument specifies the name of the cloud service provider. The argument accepts one of the following values: "AmazonS3", "AzureBlob" or "GoogleStorage".

@filename

Specifies a backup location (e.g. C:\backups\AdventureWorks.bak). This argument accepts network destinations. You can supply multiple instances of this argument to use stripe backups.

@filenumber

Disk restores:

Specifies the particular backup to use when recasting, restoring, extracting or reading from files with multiple appended backups. You can run xp_restore_headeronly to query the files contained within the backup set given by backup_file_name.

Tape restores:

Identifies the backup set to be restored. For example, a file number of 1 indicates the first backup set on the backup medium, and a file number of 2 indicates the second backup set.

@HeaderDetails

• backup—Retrieves the backup header information.
• attachedfiles—Lists files attached to a backup.
• attachedfileparams—Lists attached directories and individual files outside of those directories.
• all—Retrieves all the backup header information.

@JobP

Specifies an encrypted key. (Similar to @EncryptionKey).

You can use xp_encrypt_backup_key to convert the password (encryption_key) for use with @jobp. The original password (or encrypted key generated by xp_encrypt_restore_key) must be used to restore a backup.

@Logging

Writes a log file for the operation. This argument accepts one of the following values:

• 0—Logging off.

• 1 or any odd value—Logging on. Log file is removed on success.

• 2 or any even value—Logging on.

The default output directory is C:\Documents and Settings\All Users\Application Data\Quest Software\LiteSpeed\SQL Server\Logs (or C:\ProgramData\Quest Software\LiteSpeed\SQL Server\Logs) (or C:\ProgramData\Quest Software\LiteSpeed\SQL Server\Logs). To log to a different directory add @Trace='logpath=path'.

See Configure Logging in LiteSpeed for information about LiteSpeed logging.

@ProxyHost

The @ProxyHost argument is optional and specifies the name of the proxy host name that is running the proxy server.

@ProxyLogin

The @ProxyLogin argument is optional and specifies the proxy server login credential.

@ProxyPassword

The @ProxyPassword argument is optional and specifies the proxy server password credential.

@ProxyPasswordEnc

The @ProxyPasswordEnc argument is optional and specifies the encrypted proxy server password credential.

@ProxyPort

The @ProxyPort argument is optional and contains the port number of the proxy server. The TCP/IP port values can be 1-65535.

@tsmarchive

Specifies to store the backup as a TSM archive. This argument accepts one of the following values:

• 0—False (default)
• 1—True

@tsmconfigfile

Specifies the TSM configuration file.

@tsmclientnode

Specifies the TSM server LiteSpeed connects to during backups and restores. Not required, if specified in the options file or if backing up with the Passwordaccess Generate option.

@tsmclientownerpwd

Specifies the TSM client owner user password. Not required, if specified in the options file or if backing up with the Passwordaccess Generate option.

@tsmobject

Defines the TSM filespace, high level and low level. This argument accepts the following format:

tsm_filespace\tsm_high_level\tsm_low_level

where:

• tsm_filespace is the logical space on the TSM server that contains a group of files. It can be the drive label name or UNC name.

• tsm_high_level specifies the directory path in which the file belongs.

• tsm_low_level specifies actual name of the file.

NOTE: You may only store one item the location specified by this argument. It is not possible to append an object to this location. You can use the -I command-line argument or @init to back up to a non-unique location.

@tsmpointintime

Specifies the date for restore/to filter results. If it is not passed, LiteSpeed will choose the most recent archived backup. The format is yyyy-mm-dd hh:mm:ss.

NOTE: If the backup was a striped backup and the point-in-times of the various striped files are different (rare but can be different a second or so), then the most recent of the times must be chosen.

@Unload

Applies to tape backups and restores. This argument accepts one of the following values:

• 0—Keep tape loaded (default)

• 1—Unload and eject tape from the drive after operation

@UseSSL

The @UseSSL argument specifies that the connection uses SSL security. This argument accepts one of the following values:

• 0—Do not use SSL
• 1—Use SSL (default)

Examples

1. Display backup set information and attached files:

   exec master.dbo.xp_restore_headeronly
   @filename = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\FASTCOMP_full2.bak'
   , @attachedfiles = 2

2. List files attached to a tsm backup:

   exec master.dbo.xp_restore_headeronly
   @tsmclientnode = N'10.1.26.177',
   @tsmclientownerpwd = N'Quest2013',
   @tsmconfigfile = N'D:\dsm.opt'
   , @tsmobject = N'test\test\model'
   , @attachedfiles = 1
   

3. Restore headers from an AmazonS3 Cloud backup:

   exec master.dbo.xp_restore_headeronly
   @CloudVendor = N'AmazonS3'
   , @FileName = N'AA_3_1.bak'
   , @CloudBucketName = N'aabucket7'
   , @CloudAccessKey = N'***' -- my key
   , @CloudSecretKey = ***' -- my key
   , @CloudRegionName = N'us-west-2' -- us-east-1, us-west-2, us-west-1, eu-west-1, ap-southeast-1, ap-southeast-2, ap-northeast-1, sa-east-1
   (this is an optional parameter. Region for selected @CloudBucketName will be used.)
   , @ProxyHost = N'proxy.sitelocal'
   , @ProxyPort = 8080
   , @ProxyLogin = N'DOMAIN\xyz-tst-MYTester'
   , @ProxyPassword = N'***'

Results

xp_restore_headeronly displays the following information:

Returns

0 (success) or non-zero (failure). Return codes represent the native error number returned from SQL Server for any errors encountered during the operation.

To capture the output message, run the following:

declare @rmsg varchar(999)
exec master..<procedure_name> <arguments>, @resultmsg=@rmsg output
select @rmsg

To capture the output message and the result code, run the following:

declare @rc int
declare @rmsg varchar(999)
exec master..<procedure_name> <arguments>, @resultmsg=@rmsg output, @resultcode=@rc output
select @rc, @rmsg

Related Topic

About Using Extended Stored Procedures