Chat now with support
Chat with Support

LiteSpeed for SQL Server 8.5 - Configure Log Shipping Guide

xp_slsSmartCleanup

Removes full and differential backup files and transaction log backups based on a user-defined period (either the file age or the date).

The backup retention will never delete:

  • The backup files, if there are mixed backups in the same backup file. For example, if a user performs a backup of AdventureWorks and Pubs into the same mybackups.bak backup file.
  • The full backup, if there are associated differential or t-log backups in the backup set that are not eligible for cleanup.
  • File/FileGroup backups
  • File/FileGroup differential backups
  • Partial backups
  • Partial differential backups
  • Files that have the filesystem archive bit set (if that option is selected)

Syntax

exec master.dbo.xp_slsSmartCleanup
@database = 'database_name'
, (@BackupRetainDays = 1...365 | @BackupExpiration = 'date_time')
, (@LogRetainDays = 1...365 | @LogExpiration = 'date_time')
[, @KeepArchiveFiles = 0 | 1 ]
[, @CopyOnlyBackups = 'option']
[, @DryRun = 0 | 1 ]
[, @TSMClientNode = 'TSM_client_node']
[, @TSMUserName = 'TSM_username_ID']
[, @TSMPassword = 'TSM_username_password']
[, @TSMConfigFile = 'TSM_configuration_file']
[, @TSMClientOwnerPwd = 'TSM_client_owner_password']
[, @TSMDSMI_DIR = 'path']
[, @TSMDSMI_LOG = 'path']
[, @TSMLogName = 'log_name']

xp_slsSmartCleanup (Amazon S3)

exec master.dbo.xp_slsSmartCleanup
@database = N'test',
@CloudVendor = N'AmazonS3',
@CloudBucketName = N'test',
@CloudAccessKeyEnc = N'********',
@CloudSecretKeyEnc = N'*******',
@CloudRegionName = N'us-east-1',
@UseSSL = 1,
@BackupRetainDays = 28,
@LogRetainDays = 7

xp_slsSmartCleanup (Azure)

exec master.dbo.xp_slsSmartCleanup
@database = N'test',
@CloudVendor = N'AzureBlob',
@CloudAccessKeyEnc = N'******',
@CloudSecretKeyEnc = N'*****',
@CloudBucketName = N'test',
@UseSSL = 1,
@BackupRetainDays = 28,
@LogRetainDays = 7

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.

Important: This @AWSAccessKey argument is replaced by @CloudAccessKey. The @AWSAccessKey argument is no longer valid in subsequent LiteSpeed versions after 8.2.

@AWSAccessKeyEnc

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

Important: This @AWSAccessKeyEnc argument is replaced by @CloudAccessKeyEnc. The @AWSAccessKeyEnc argument is no longer valid in subsequent LiteSpeed versions after 8.2.

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

Important: This @AWSBucketName argument is replaced by @CloudBucketName. The @AWSBucketName argument is no longer valid in subsequent LiteSpeed versions after 8.2.

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

Important: This @AWSRegionName argument is replaced by @CloudRegionName. The @AWSRegionName argument is no longer valid in subsequent LiteSpeed versions after 8.2.

@AWSSecretKey

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

Important: This @AWSSecretKey argument is replaced by @CloudSecretKey. The @AWSSecretKey argument is no longer valid in subsequent LiteSpeed versions after 8.2.

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

Important: This @AWSSecretKeyEnc argument is replaced by @CloudSecretKeyEnc. The @AWSSecretKeyEnc is no longer valid in subsequent LiteSpeed versions after 8.2.

@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

Important: This @AWSUseGovCloud argument is replaced by @CloudGovRegion. The @AWSUseGovCloud argument is no longer valid in subsequent LiteSpeed versions after 8.2.

@BackupExpiration

Specifies the date using one of the following formats:

YYYY-MM-DD

YYYY-MM-DD HH:MM:SS

where

  • YYYY—4-digit year
  • MM—2-digit month
  • DD—2-digit day of the month
  • HH—2-digit hour using the local 24-hour clock
  • MM—2-digit minute
  • SS—2-digit second

To be eligible for cleanup, the full or differential backup must be older than this date.

@BackupRetainDays

Specifies the number of days (N). The full or differential backup must be at least N days old before it is eligible for cleanup.

@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 or Azure Account Name.

@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 or Azure Container Name.

@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, eu-west-1, ap-south-1, ap-southeast-1, ap-southeast-2, ap-northeast-1, and sa-east-1.

@CloudSecretKey

The @CloudSecretKey argument specifies the name of the Cloud Web Service secret key that is assigned when you initially get a Cloud account. The selections are Amazon Secret Key or Azure Access Key.

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

@database

Name of database to be backed up or restored.

This parameter specifies a database:

  • to be backed up (xp_backup_database and xp_slsFastCompression)

  • containing the transaction log to be backed up (xp_backup_log)

  • to be restored (xp_restore_database and xp_restore_log)

  • on which you wish to check the progress of an activity (xp_slsReadProgress)

  • for which you want to delete old backups (xp_slsSmartCleanup)

If supplied as a variable (@database), this name can be specified either as a string constant (@database = database name) or as a variable of character string data type, except for the ntext or text data types.

@CloudVendor

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

@CopyOnlyBackups

Controls how LiteSpeed handles copy-only backups. This argument accepts one of the following values:

  • Default—LiteSpeed will ignore copy-only backups except on secondary replicas in AlwaysOn Availability groups, in which case it will allow deletions. This is the default behavior when the parameter is not specified.
  • Ignore—Copy-only backups are never deleted.
  • AllowDeletes—Copy-only backups are removed according to the specified retention options.

NOTES:

  • Transaction log backups are not considered dependent on copy-only full or copy-only tlog backups.
  • Copy-only transaction log backups will not mark other transaction log or full backups as having a dependent.
  • The values are not case-sensitive.

@DryRun

Displays backups that are to be removed (delete candidates) or kept according to the specified conditions and SmartCleanup logic. SmartCleanup does not remove any backups, if this parameter is specified.

@KeepArchiveFiles

Turns on monitoring and refuses to delete files that have the archive filesystem bit set. When enabled dependent files are not deleted.

@LogExpiration

Specifies the date of one of the following formats:

YYYY-MM-DD

YYYY-MM-DD HH:MM:SS

where

  • YYYY—4-digit year
  • MM—2-digit month
  • DD—2-digit day of the month
  • HH—2-digit hour using the local 24-hour clock
  • MM—2-digit minute
  • SS—2-digit second

To be eligible for cleanup, the t-log backup must be older than this date.

@LogRetainDays

Specifies the number of days (N). The t-log backup must be at least N days old before it is eligible for cleanup.

@MultiDatabaseType

Produces a cleanup that includes several types of databases. Types can include: all, system, user, or selected databases.

This argument accepts one of the following values:

  • All - Clean up backups for all system and user databases.
  • System - Clean up backups for only system databases.
  • User - Clean up backups for only user databases.
  • Selected - Clean up backups for specifically selected databases.

@ProxyHost

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

note: If the @ProxyHost argument is not defined, then the LiteSpeed core engine checks the local .ini files for the proxy parameters. If the proxy parameters are not detected, then the LiteSpeed core engine connects directly to the proxy servers.

@ProxyLogin

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

note: If not defined, then the LiteSpeed core engine checks the local .ini files for the proxy parameters. If the proxy parameters are not detected, then the LiteSpeed core engine connects directly to the proxy servers.

@ProxyPassword

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

note: If the @ProxyPassword argument is not defined, then the LiteSpeed core engine checks the local .ini files for the proxy parameters. If the proxy parameters are not detected, then the LiteSpeed core engine connects directly to the proxy servers.

@ProxyPasswordEnc

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

note: If the @ProxyPasswordEnc argument is not defined, then the LiteSpeed core engine checks the local .ini files for the proxy parameters. If the proxy parameters are not detected, then the LiteSpeed core engine connects directly to the proxy servers.

@ProxyPort

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

note: If the @ProxyPort argument is not defined, then the LiteSpeed core engine checks the local .ini files for the proxy parameters. If the proxy parameters are not detected, then the LiteSpeed core engine connects directly to the proxy servers.

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

@tsmdsmi_dir

DSMI_DIR path if needed.

@tsmdsmi_log

DSMI_LOG path.

@tsmlogname

Log name.

@tsmpassword

The TSM username password. Passwords are case-sensitive.

@tsmusername

The TSM username ID.

@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. Delete disk full and differential backups older than 28 days, delete log backups older than 2 days, allow deletions of the copy-only backups:

    exec master.dbo.xp_slsSmartCleanup
    @database = 'test2'
    , @BackupRetainDays = 28
    , @LogRetainDays = 2
    , @CopyOnlyBackups = 'AllowDeletes'

  2. Delete disk full and differential backups created before 11/15/2012:

    exec master.dbo.xp_slsSmartCleanup
    @database = 'test2'
    , @BackupExpiration = '2012-11-15'

  3. Delete tsm log backups older than 2 days:

    exec master.dbo.xp_slsSmartCleanup
    @database = N'test_tsm'
    , @tsmconfigfile = N'C:\Program Files\Tivoli\TSM\baclient\dsm.opt'
    , @tsmclientnode = N'w2k3_TSM2'
    , @tsmclientownerpwd = N'*****'
    , @LogRetainDays = 2

  4. Delete full, differential and transaction log TSM backups created before 06/15/2012, using the PASSWORDAccess generate option to connect to the TSM Server:

    exec master.dbo.xp_slsSmartCleanup
    @database = N'test_tsm'
    , @tsmconfigfile = N'C:\Program Files\Tivoli\TSM\baclient\dsm_gp.opt'
    , @BackupExpiration = '2012-06-15'
    , @LogExpiration = '2012-06-15'

  5. Keep archived files older than 2 days:

    exec master.dbo.xp_slsSmartCleanup
    @database = @dbname
    , @BackupRetainDays = 2
    , @LogRetainDays = 1
    , @KeepArchiveFiles = 1

  6. Microsoft Azure cloud cleanup

    exec master.dbo.xp_slsSmartCleanup

    @database = N'test',

    @CloudVendor = N'AzureBlob',

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

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

    @CloudBucketName = N'test',

    @UseSSL = 1,

    @BackupRetainDays = 28,

    @LogRetainDays = 7

Returns

0 (success) or non-zero (failure).

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 Documents