LiteSpeed for SQL Server 8.9.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)

This topic covers:

• Syntax

• Arguments

• Examples

• Returns

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'] [, @Locations = N'C:\Backups',]
[, @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']

Note: Parameters @BackupRetainDays and @LogRetainDays replaced with @BackupRetain + @BackupRetainUnits and @LogRetain + @LogRetainUnits. However, parameters @BackupRetainDays and @LogRetainDays are still supported for comparability reasons.

xp_slsSmartCleanup (Amazon S3)

exec master.dbo.xp_slsSmartCleanup

@database = N'DatabaseName',

@CloudVendor = N'AmazonS3',

@CloudBucketName = N'bucket1',

@CloudAccessKeyEnc = N'<Encrypted Key1>',

@CloudSecretKeyEnc = N'<Encrypted Key2>',

@CloudRegionName = N'us-west-1',

@UseSSL = 1,

@BackupRetain = 28,

@BackupRetainUnits = N'day',

@LogRetain = 7,

@LogRetainUnits = N'day',

@Locations = N'/'

xp_slsSmartCleanup (Microsoft Azure)

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

@BackupRetain = 28,
@BackupRetainUnits = N'day',
@LogRetain = 7,
@LogRetainUnits = N'day',
@Locations = N'/'

xp_slsSmartCleanup (Google Storage)

exec master.dbo.xp_slsSmartCleanup
@database = N'test',
@CloudVendor = N'GoogleStorage',
@CloudAccessKeyEnc = N'******',
@CloudSecretKeyEnc = N'*****',
@CloudBucketName = N'test',
@UseSSL = 1,

@BackupRetain = 28,
@BackupRetainUnits = N'day',
@LogRetain = 7,
@LogRetainUnits = N'day',
@Locations = N'/'

xp_slsSmartCleanup (S3 Compatible Storage)

exec master.dbo.xp_slsSmartCleanup
@database = N'test',
@CloudVendor = N'S3Compatible',
@CloudEndpoint = N'storageserver:port',
@CloudAccessKeyEnc = N'******',
@CloudSecretKeyEnc = N'*****',
@CloudBucketName = N'test',
@UseSSL = 1,

@BackupRetain = 28,
@BackupRetainUnits = N'day',
@LogRetain = 7,
@LogRetainUnits = N'day',
@Locations = N'/'

xp_slsSmartCleanup (Disk)

exec master.dbo.xp_slsSmartCleanup

@database = N'DatabaseName',

@BackupRetain = 28,

@BackupRetainUnits = N''day'',

@LogRetain = 7,

@LogRetainUnits = N''day'',

@Locations = N'C:\Backups',

@KeepArchiveFiles = 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>

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

@BackupRetain

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

See BackupRetainUnits for unit types details

Note: Old argument BackupRetainDays is still supported for compatibility reasons.

@BackupRetainUnits

Defines unit type for BackupRetain argument.

Allowed values: hour / day / week / month / year.

Note: Month = 30 days, Year = 365 days

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

@LogRetain

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

See also LogRetainUnits for unit types details

Note: Old argument @LogRetainDays is still supported for compatibility reasons.

@LogRetainUnits

Defines unit type for LogRetain argument.

Allowed values: hour / day / week / month / year.

Note: Month = 30 days, Year = 365 days

@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, S3 Compatible Storage Access Key.

@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, S3 Compatible Storage Bucket 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, 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", "GoogleStorage" or "S3Compatible".

@CloudEndpoint

The @CloudEndpoint argument specifies the endpoint for S3 Compatible Storage and custom endpoint for Amazon S3 service (in case of VPC). Format: servername:port .Example values: "servername", "servername:port", "ip:port", "10.0.0.200:9021".

@CloudAcceptAllCertificates

The @CloudAcceptAllCertificates argument instructs LiteSpeed accept self-signed certificates for S3 Compatible Storage. This argument accepts one of the following values:

• 0—Accept verified certificates only (default)
• 1—Accept self-signed certificates

Note: Delete backups is not supported in Maintenance Plans with this option.

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

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

@Destination

Specifies the destination where to delete files from. Possible values: All, Disk, TSM, Tape, Cloud.

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

@GSProject

DEPRECATED LiteSpeed 8.8: Was used to store for the Google Cloud Storage project ID; the project ID is now obtained from login. This parameter is retained for compatibility with old backup/restore scripts.

@KeepArchiveFiles

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

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

@Locations

Defines locations (spread by semicolon symbol) to apply cleanup policy.

Disk: “F:\\Backups; F:\\mirrors”

Cloud folders: "fff\; test123\"

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

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

You can use the %TSMDEFAULTPATH% variable to make LiteSpeed detect the default TSM configuration file path automatically (by getting from LiteSpeed defaults as a priority or the registry - HKEY_LOCAL_MACHINE\SOFTWARE\IBM\ADSM\CurrentVersion\BackupClient)

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

@ReviewAllBackups

Specifies Smart Cleanup behavior for searching backups to delete

1. 0 - Save last deleted backup ID and start check backups from this backup next time (default).
2. 1 - Review all backups from MSDB backups history (takes more time)

@NoOutput

Disable output information for the procedure

1. 0 - Output information enabled (default).
2. 1 - Output information disabled

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'
   , @BackupRetain = 2 8
   , @BackupRetainUnits = N'day'
   , @LogRetain = 2
   , @LogRetainUnits = N'day'
   , @CopyOnlyBackups = 'AllowDeletes'
   , @Locations = N'F:\Backups; F:\mirrors'

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

   exec master.dbo.xp_slsSmartCleanup
   @database = 'test2'
   , @BackupExpiration = '2019-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'*****'
   , @LogRetain = 2

4. Delete full, differential and transaction log TSM backups created before 06/15/2019, 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 = '2019-06-15'
   , @LogExpiration = '2019-06-15'
   

5. Keep archived files older than 2 days:

   exec master.dbo.xp_slsSmartCleanup
   @database = @dbname
   , @BackupRetainDays = 2
   , @LogRetain
   , @LogRetainUnits = N'day'
   , @KeepArchiveFiles = 1
   , @Locations = N'F:\Backups; F:\mirrors'

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,
   @BackupRetain = 28,
   @LogRetain = 7
   , @BackupRetainUnits = N'day'
   , @LogRetainUnits = N'day'
   , @Locations = N'folder1\; folder2'
   

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 Topics

Smart Cleanup Policies

SmartCleanup Command-Line Arguments