Deleting remote backup files after a specified time period (version 5)

Category: How do I?
Date: 15 Jul 2009
Product: SQL Backup
Versions: 5.3, 5.4

It may be desirable to make copies of a database backup to a network share as part of a SQL Backup job, but you may want to retain the copies for a longer or shorter time on the share than in the primary backup folder.

SQL Backup provides some flexibility in file management by providing a few options for retaining or erasing files via the ERASEFILES and ERASEFILES_REMOTE parameters. ERASEFILES sets the retention time for backups at the primary backup location, unless used in conjunction with FILEOPTIONS=1,3,5 or 7, which cause copies to be deleted as well. ERASEFILES and ERASEFILES_REMOTE are used together in order to specify two different retention times: one for the primary backup location and another for the copies made in the folder specified by the the COPYTO parameter.

If you have configured a backup job via SQL Backup and want to configure the file retention options independently, you are required to edit the job using SQL Server Management Studio, since the SQL Backup interface does not present the ERASEFILES_REMOTE option to you when you create the scheduled backup job using the SQL Backup software. Note that version 6 of SQL Backup does allow you to specify two individual retention times in the backup wizards without requiring you to edit the job outside of SQL backup.

Start SQL Server Management Studio, and locate the backup job in the SQL Agent jobs list. Edit the backup job step to add the ERASEFILES_REMOTE parameter as in the script below:

DECLARE @exitcode int
DECLARE @sqlerrorcode int
EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASE [TestDBTest] TO DISK = ''C:\Programs\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\<AUTO>.sqb'' WITH COMPRESSION = 1, ERASEFILES = 3, FILEOPTIONS = 1, ERASEFILES_REMOTE = 30"', @exitcode OUT, @sqlerrorcode OUT
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END

The FILEOPTIONS command tells SQL Backup to also delete files in the COPYTO location, the ERASEFILES_REMOTE specifies the number of days to retain files in the COPYTO location if you want this to be different from the primary location.