January 1, 2010 at 8:53 pm
I'm trying to have a rolling 2 days' worth of backups on the backup HD (with nightly copies off to tape, but it's nice to have the files available w/o loading a tape, and this gives me redundancy in case a tape backup fails). Only the most recent 2 days' backups are left on the file server, and the old ones get purged nightly.
So, I figured RETAINDAYS would work, but per BOL and others, it only marks backups in a file as expired but doesn't actually delete them. I verified this by running many backups to one file over several days, and the backup file keeps growing in size, even though only 2 jobs are inside their ExpirationDate. The "expired" backups are still viable for point-in-time recoveries, too.
OK. Then others recommended looking at Maint. Plan Cleanup task, but this uses xp_delete_file, which deletes an entire file off the disk - not what I want either.
So, what's the "best" way to have the most recent 2 days - and only that - retained on disk? I could set up a script w/ VBS or batch file or SSIS or some such, but I keep thinking T-SQL must have something that would allow a completely SQL solution w/o bolting on a batch file. Or should I just put each night's backup to its own uniquely named file (prob. w/ date in its name) so that the Cleanup Task can work as designed?
Thanks very much for any assistance!
Rich
January 2, 2010 at 9:35 am
rmechaber (1/1/2010)
I'm trying to have a rolling 2 days' worth of backups on the backup HD (with nightly copies off to tape, but it's nice to have the files available w/o loading a tape, and this gives me redundancy in case a tape backup fails). Only the most recent 2 days' backups are left on the file server, and the old ones get purged nightly.So, I figured RETAINDAYS would work, but per BOL and others, it only marks backups in a file as expired but doesn't actually delete them. I verified this by running many backups to one file over several days, and the backup file keeps growing in size, even though only 2 jobs are inside their ExpirationDate. The "expired" backups are still viable for point-in-time recoveries, too.
OK. Then others recommended looking at Maint. Plan Cleanup task, but this uses xp_delete_file, which deletes an entire file off the disk - not what I want either.
So, what's the "best" way to have the most recent 2 days - and only that - retained on disk? I could set up a script w/ VBS or batch file or SSIS or some such, but I keep thinking T-SQL must have something that would allow a completely SQL solution w/o bolting on a batch file. Or should I just put each night's backup to its own uniquely named file (prob. w/ date in its name) so that the Cleanup Task can work as designed?
Thanks very much for any assistance!
Rich
The best way is to not use a device or a single file to contain your backups. Set your backups to use a new file every time, then you can use the maintenance cleanup task to remove files older that a certain time.
Manage your backups in a folder instead of trying to manage them in a file - works much better and gives you the simple ability to look in the directory and see what files you have available.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 3, 2010 at 4:42 pm
It is somewhat unclear how your backups are performed. Are new backup files being created every time a backup occurs, or are they appended to files? Fully understanding your backup schedule will help troubleshoot this issue much better.
Joie Andrew
"Since 1982"
January 3, 2010 at 6:30 pm
Jeffrey, Joie, thanks.
Yes, for my testing, I've been putting multiple backups into one file with append. I figured that, if SQL Server had built-in capability for culling expired backups from a single file, that I'd just put them into one file on successive days and let SQL Server clean them out. Looks like I'll stick w/ one backup per file and use a different task to remove old files.
Follow-up question: do you also recommend a separate file for each log backup, too?
Thanks to you both,
Rich
January 3, 2010 at 11:31 pm
Yes.
Joie Andrew
"Since 1982"
January 4, 2010 at 7:04 am
Yes, absolutely use a separate file for each log backup also. I have seen people use devices and initialize the device right after they backup their system - without realizing that doing so eliminates the ability to recover to a point in time prior to that backup.
Separate files gives you the ability to manage that fully yourself.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 4, 2010 at 7:08 am
Fully agree, one file per full, differential, and transaction log backups.
January 4, 2010 at 7:43 am
Thanks again, all!
So..... if "everyone" recommends putting only 1 backup into 1 file, of what use is the Append (NOINIT) option? Is this simply a legacy from backing up directly to serial storage like tape (and added as an option for backup to disk)?
Rich
January 4, 2010 at 10:45 am
I believe so. I think that many of the commercial backup software still use SQL's APIs to backup databases. If people are backing up to tape, then the appending to the backup would make sense.
Joie Andrew
"Since 1982"
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply