Inconsistent erasing of old backup files with v5.1

alchemistmatt

As discussed in
http://www.red-gate.com/MessageBoard/viewtopic.php?t=4959 v5.0 was not erasing old backup files when using ERASEFILES. I have now installed v5.1 and have discovered that when I provide a list of databases to backup to the sqlbackup extended SP, the old backups are only being deleted for every other database backed up. Here's the Sql used:

exec master..sqlbackup '-SQL "BACKUP LOGS [DatabaseA,DatabaseB,DatabaseC,DatabaseD,DatabaseE] TO DISK = ''\\BackupServer\BackupShare\<DATABASE>\<AUTO>'' WITH NAME=''<AUTO>'', DESCRIPTION=''<AUTO>'', MAXDATABLOCK=524288, ERASEFILES=20, FILEOPTIONS=3, COMPRESSION=3, THREADCOUNT=2, VERIFY, LOGTO=''I:\SqlServerBackup\'', MAILTO_ONERROR = ''user@company.com''"'

I can send the log file to somebody if they'd like to look through it. The OS is Windows 2003 server, 64 bit, and I'm using Sql Server 2005 64 bit. Yes, I'm backing up directly to a remote share, but I also tried backing up locally and then using COPYTO and the same every-other behavior was observed.

HJoyce

Hi Matt,

I will try to reproduce this here, but so far I'm unable to.

I noticed you have FILEOPTIONS=3 in your command which will mean that both the following rules apply

1. Delete old backup files in the secondary backup folders (specified using COPYTO) if they are older than the number of days or hours specified in ERASEFILES or ERASEFILES_ATSTART.

2. Delete old backup files in the primary backup folder (specified using DISK) if they are older than the number of days or hours specified in ERASEFILES or ERASEFILES_ATSTART, unless they have the ARCHIVE flag set.

Is it possible that the second rule is being applied so that it doesn't delete the backup files?

Regards,
Helen

alchemistmatt

When using v4.5 of Sql Backup I was backing up directly to a network share, was using ERASEFILES, but was not using FILEOPTIONS. With v5.0, I could still backup directly to a network share on 3 of my 4 servers, but not on the fourth (getting error "Process terminated unexpectedly. Error code: -2139684860"), and so I switched to backing up to a local disk and using COPYTO (also, a post mentioned this is faster and safer than backing up multi-GB databases directly to a remote share).

Since I'm now backing up both to a local folder and then copying to a network share, I set FILEOPTIONS to 3, since I wanted old backups to be deleted from both the COPYTO folder (FILEOPTIONS=1) and the DISK folder (FILEOPTIONS=2). Based on your suggestion, I'll try FILEOPTIONS=1 and see if the trend is still there (I can wipe the local backup folder daily, so simply having Sql Backup auto-delete old files in the COPYTO folder will be acceptable).

I had not noticed the archive flag exception for FILEOPTIONS=2 or 3; good to learn about. Still, the every-other-DB deletion trend is occuring both with or without the use of COPYTO, and is occurring regardless of the archive bit (the program is deleting files in a COPYTO folder even when the Archive bit is set since all of my backup files have the Archive bit set).