Log shipping jobs not deleting old .sqb files

Category: Troubleshooting & error messages
Date: 09 Jun 2009
Product: SQL Backup
Versions: 5.0,5.1,5.2,5.3

Log shipping jobs are not deleting old .sqb files from the log shipping share.

To remove old backup files from the 'MOVETO' location you need to set the 'FILEOPTIONS' extended argument in addition using the 'ERASEFILES' argument.

ERASEFILES

Deletes all SQL Backup files for the database that are older than the specified number of days (or hours), and are located in the MOVETO folder. You must also set the FILEOPTIONS argument. The files are deleted only if the name of the SQL Server, instance (if applicable), and database recorded in the file header match the details of the database that is being restored.

To specify the age of files to be deleted in hours, type h after the number. For example, ERASEFILES = '24' deletes files that are more than 24 days old; ERASEFILES = '24h' deletes files that are more than 24 hours old.

FILEOPTIONS

Specifies whether old backup files are to be deleted in the primary backup folder and the MOVETO folder. Specify the sum of the values that correspond to the options you require:

1 Delete old backup files in the MOVETO folder if they are older than the number of days or hours specified in ERASEFILES.
2 Do not delete old backup files in the MOVETO folder that are older than the number of days or hours specified in ERASEFILES if they have the ARCHIVE flag set.

Valid values are 1, 2, and 3.

ERASEFILES_REMOTE (SQL Backup version 5.3)

This option is the same as ERASEFILES, however the deletion will only take place on the backups copied to the log shipping share, leaving any backups on the local disk.

You must also set the age of the files to delete using ERASEFILES. For example, to delete old backup files in the MOVETO folder that are older than 5 days:

RESTORE DATABASE ... WITH MOVETO = ... , ERASEFILES = 5, FILEOPTIONS = 1

To delete any existing files in the MOVETO folder that are older than 5 days and do not have the ARCHIVE flag set, (values 1 + 2):

RESTORE DATABASE ... WITH MOVETO = ... , ERASEFILES = 5, FILEOPTIONS = 3

So using the syntax provided your restore script may be as follows:

DECLARE @errorCode INT
DECLARE @sqlerrorCode INT
EXECUTE master..sqlbackup N'-SQL "RESTORE LOG [BDEventData] FROM DISK = ''D:\MSSQL.1\MSSQL\Backup\BDEventData\LOG_BDEventData_*.sqb'' WITH ERASEFILES = 1, FILEOPTIONS = 1, STANDBY = ''D:\MSSQL.1\MSSQL\Backup\UNDO_BDEventData.dat'', MOVETO = ''D:\MSSQL.1\MSSQL\Backup\BDEventData\Processed''"', @errorCode OUT, @sqlerrorCode OUT;
IF (@errorCode >= 500) OR (@sqlerrorCode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @errorCode, @sqlerrorcode)
END