ERASEFILES = 1 ???

Betelgeuse

I want only one copy of backups on disk but with ERASEFILES = 1, the previous night's backups are NOT deleted. I could use ERASEFILES = 23h, but I have a lot of Agent jobs to edit...

In Enterprise Manager, the correlary is "remove files older than x days", which when set to 1 gives me the desired result of a single backup file in the directory.

petey

Are the last modified dates of the 2 backup files more than 24 hours apart? SQL Backup compares the last modified dates of the backup files to determine when to delete files, while I think EM compares the creation date.

SQL Backup works this way because of an 'eccentric' design decision I made. A database backup contains the state of the database at the time of backup completion. If you wanted to retain backups that allowed you to restore the state of the database up to 24 hours ago, you would need to retain all backup files that were modified in the last 24 hours.

E.g.

Day 1 backup starts at 2 a.m, completes at 2:15 a.m.
Day 2 backup starts at 2 a.m, completes at 2:14 a.m.

Day 1's backup allows you to restore the database to a state that's 23 hrs and 59 mins behind the current backup, hence it's retained by SQL Backup.

Are you using the multiple database backup option e.g.

BACKUP DATABASES [...] ...

This may be able to reduce the number of Agent jobs you have to maintain.

Betelgeuse

I understand the reasoning behind your implementation, and I appreciate the added granularity SQL Backup provides with retention measured in hours in addition to days.

However, with all due respect, I find your implementation a bit too esoteric. Since SQL Backup is replacing functionality within EM, it makes sense to me to model the behavior of your application after theirs. I'm all for having improvements, but I don't think changing the fundamental meaning of what a "day" constitutes is valuable or necessary. I think it better to let the DBA figure out what they want to retain and what they want to discard and then give them intuitive tools to get the job done.

Though well-intentioned, I don't think your implementation is the least bit intuitive.

Anyway, I'm using an ERASEFILES value of 23h now instead of 1 day and I'm using a single script for all my databases so everything is working to my satisfaction.

Thanks!
Neil