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:
Files that have the filesystem archive bit set (if that option is selected)
This topic covers:
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.
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'/'
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'/'
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'/'
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'/'
exec master.dbo.xp_slsSmartCleanup
@database = N'DatabaseName',
@BackupRetain = 28,
@BackupRetainUnits = N''day'',
@LogRetain = 7,
@LogRetainUnits = N''day'',
@Locations = N'C:\Backups',
@KeepArchiveFiles = 1
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>
Specifies the date using one of the following formats:
YYYY-MM-DD
YYYY-MM-DD HH:MM:SS
where
To be eligible for cleanup, the full or differential backup must be older than this date.
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.
Defines unit type for BackupRetain argument.
Allowed values: hour / day / week / month / year.
Note: Month = 30 days, Year = 365 days
Specifies the date of one of the following formats:
YYYY-MM-DD
YYYY-MM-DD HH:MM:SS
where
To be eligible for cleanup, the t-log backup must be older than this date.
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.
Defines unit type for LogRetain argument.
Allowed values: hour / day / week / month / year.
Note: Month = 30 days, Year = 365 days
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.
The @CloudAccessKeyEnc argument specifies the name of the encrypted unique Cloud Web Service alphanumeric access key that identifies each user.
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,
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:
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'.
The @CloudSecretKey argument specifies the name of the Cloud Web Service secret key that is assigned when you initially get a Cloud account.
The @CloudSecretKeyEnc argument specifies the name of the encrypted Cloud Web Service secret key that is assigned when you initially get a Cloud account.
The @CloudVendor argument specifies the name of the cloud service provider. The argument accepts one of the following values: "AmazonS3", "AzureBlob", "GoogleStorage" or "S3Compatible".
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".
The @CloudAcceptAllCertificates argument instructs LiteSpeed accept self-signed certificates for S3 Compatible Storage. This argument accepts one of the following values:
Note: Delete backups is not supported in Maintenance Plans with this option.
Controls how LiteSpeed handles copy-only backups. This argument accepts one of the following values:
NOTES:
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.
Specifies the destination where to delete files from. Possible values: All, Disk, TSM, Tape, Cloud.
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.
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.
Turns on monitoring and refuses to delete files that have the archive filesystem bit set. When enabled dependent files are not deleted.
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:
Defines locations (spread by semicolon symbol) to apply cleanup policy.
Disk: “F:\\Backups; F:\\mirrors”
Cloud folders: "fff\; test123\"
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. |
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. |
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. |
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. |
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. |
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.
Specifies the TSM client owner user password. Not required, if specified in the options file or if backing up with the Passwordaccess Generate option.
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)
DSMI_DIR path if needed.
DSMI_LOG path.
Log name.
The TSM username password. Passwords are case-sensitive.
The TSM username ID.
The @UseSSL argument specifies that the connection uses SSL security. This argument accepts one of the following values:
Specifies Smart Cleanup behavior for searching backups to delete
Disable output information for the procedure
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'
Delete disk full and differential backups created before 11/15/2019:
exec master.dbo.xp_slsSmartCleanup
@database = 'test2'
, @BackupExpiration = '2019-11-15'
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
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'
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'
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'
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
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center