Scheduled backup - Not deleting previous versions

kleesman

I had a scheduled backup on a server that runs at 12:05 am every night. It backs up 8 databases and it is supposed to delete backup files over 1 day old. Basically, I only want the most recent backup stored, as the folder they're backed up to is written to DVD later in the morning.

I'm finding that the file deletion is inconsistent. For example last night, only 2 of the 8 previous backups were deleted. I checked the log file and it shows that the first and the last databases had their previous file deleted.

Is it possible that this is somehow time related? Would a backup file being 23 hours and 58 minutes old be treated differently than a file 24 hours and 2 minutes old, when the delete backup files older than 1 days is selected?

Thanks,
Kyle

petey

Yes. SQL Backup only deletes old files if the current backup succeeds (unless you use the ERASEFILES_ATSTART option), and uses the backup completion date/time as reference. Given that backup times may vary based on a wide variety of factors (CPU utilisation, disk workload etc), a granularity of 1 day is insufficient if you want to retain only the most current backup.

E.g. day 1 backup completes at 12:10 a.m. Day 2 backup completes the next day, at 12:09 a.m. SQL Backup will only delete files older created before 12:09 a.m the previous day.

Which is why in version 4 we added the option to specify the age in hours.

kleesman

Thanks for the quick reply. That makes sense. I'll switch it to 12 hours to make sure I remove yesterday's files.

Kyle