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']
[, @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']
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
exec master.dbo.xp_slsSmartCleanup
@database = N'test',
@CloudVendor = N'AzureBlob',
@CloudAccessKeyEnc = N'******',
@CloudSecretKeyEnc = N'*****',
@CloudBucketName = N'test',
@UseSSL = 1,
@BackupRetainDays = 28,
@LogRetainDays = 7
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>
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. |
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. |
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. |
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. |
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. |
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. |
The @AWSUseGovCloud argument enables a special restricted region for the US Government use in Amazon S3. This argument accepts one of the following values:
Important: This @AWSUseGovCloud argument is replaced by @CloudGovRegion. The @AWSUseGovCloud argument is no longer valid in subsequent LiteSpeed versions after 8.2. |
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 days (N). The full or differential backup must be at least N days old before it is eligible for cleanup.
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.
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.
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" or "GoogleStorage".
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.
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 days (N). The t-log backup must be at least N days old before it is eligible for cleanup.
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:
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:
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'
Delete disk full and differential backups created before 11/15/2012:
exec master.dbo.xp_slsSmartCleanup
@database = 'test2'
, @BackupExpiration = '2012-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'*****'
, @LogRetainDays = 2
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'
Keep archived files older than 2 days:
exec master.dbo.xp_slsSmartCleanup
@database = @dbname
, @BackupRetainDays = 2
, @LogRetainDays = 1
, @KeepArchiveFiles = 1
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
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 Conditions d’utilisation Confidentialité Cookie Preference Center