LiteSpeed for SQL Server 8.9 - Integration with TSM Guide

xp_restore_filelistonly

Returns a result set with a list of the database and log files contained in a LiteSpeed backup set.

This topic covers:

• Syntax

• Arguments

• Results

• Returns

Syntax

xp_restore_filelistonly (Disk)

EXEC master.dbo.xp_restore_filelistonly

( @filename = 'backup_file_name') [,...n]

[, @filenumber = n]

xp_restore_filelistonly (TSM)

EXEC master.dbo.xp_restore_filelistonly

( @tsmobject = 'TSM_object') [,...n]

, @tsmconfigfile = 'TSM_configuration_file'

[, @filenumber = n]

[, @tsmclientnode = 'TSM_client_node']

[, @tsmclientownerpwd = 'TSM_client_owner_password']

[, @tsmpointintime = 'date_time' ]

xp_restore_filelistonly (Tape)

EXEC master.dbo.xp_restore_filelistonly
@filename = 'tape_device_name'
[, @filenumber = n]

xp_restore_filelistonly (Amazon S3)

exec master.dbo.xp_restore_filelistonly

@filename = N'test/test/test.bkp',

@filenumber = 1,

@CloudVendor = N'AmazonS3',

@CloudBucketName = N'california',

@CloudAccessKey = N'***********',

@CloudSecretKey = N'****************',

@UseSSL = 1

xp_restore_filelistonly (Microsoft Azure)

exec master.dbo.xp_restore_filelistonly

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

@CloudVendor = N'AzureBlob',

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

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

@CloudBucketName = N'test',@UseSSL = 1

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>

@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

@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.

@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

@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.

@tsmconfigfile

Specifies the TSM configuration file.

@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)

Results

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