August 25, 2011 at 3:35 pm
I've found a couple of issues with deleting old backup files. These issues seem intuitive at first, but with a little thought it seems I was very wrong!
Has anyone worked through all this and documented it up or anything??
Example -- Say you do weekly Full backups every Saturday at 12:05am, and you want to keep a month's worth of backups, you have 150 databases on your server, they add up to 1TB, and it takes about 15 hours to do backups (a real world example). So in your Maintenence Plan, just before you do your backup, you have a task that deletes all Full backup files that are older than a month. Seems intuituve, right?
WRONG! You end up with FIVE of your backup files. (On a server with small databases and plenty of storage, this isn't a problem, but if your backup volume is tight, and you have large databases, this can be a catastrophe when you fill your backup volume!) The problem is that you are starting your backups exactly every 7 days. And what you want when your weekly Full backup finishes is 4 backup files, 4 weeks of backups, including the one you just did -- exactly 28 days.
So you actually have to delete all backups that are older than 27 days! This is because your backups don't all finish instantly at 12:05:00am on Saturday morning. The delete pretty much does, but the backup of a database might not happen until 4:00am or whatever. So 4 weeks earlier, that backup file was created at, well, not 28 days prior to 12:05am, but 28 days prior to 4:00am -- therefore the delete this week at 12:05am needs to be less than 28 days. At 12:05am backup file at 4:00am is actually about 27days, 19hours and 55minutes old, therefore a delete of files "older than 28 days" won't delete it, and certainly a delete of "older than 1 month" won't delete it at all -- and this is what I've seen many people do!
If you move the delete task to after the backups finish, then again you can't use older than 1 month, that won't work at all. And you are playing with fire if you use 28 days, because you are relying on this week's backups taking the same time as they did 4 weeks ago, but in fact it is more likely that as your databases grow so will your backup times (or there could be more stuff happening on the server), so this would miss one or more old Full backup files. Setting it to delete files older than 27 days should work to delete all the old backup files if the delete task is either before or after the backups, assuming your backups take less than 24 hours. You could set it to 26 or 25 days, actually, because you want to delete all the old backup files, and you have a full week to play with.
Is this all clear? Or am I bonkers?? Or does everyone already know all this and they just forgot to tell me??? 🙂
But wait, there's more! Let's say you're also doing hourly Transaction Log backups, every day, and they start at 1:05am. And lets say you want to keep just the last week's worth of TLog backups for a potential point-in-time restore, and the 2 weeks prior to that you just want to keep the Full backup files.
So there are two issues. Right at 12:05am when your Full backups start, there is no TLog backup happening. But the way I've seen it set up, the TLog backups Maintenence Plan is totally separate. So it starts trying to do TLog backups at 1:05am, and every hour after that. So I'm actually not sure what really happens here -- does the hourly TLog backup Maintenenace Plan start doing its thing only after a database has received this week's Full backup? Or do all TLog backups wait until after the Full backup Maintenence Plan has finished? Or does the first TLog backup Maintenence Plan sit there and wait for all the Full backups to finish, then all the intervening TLog backups are skipped???
And the other issue is, I'm not certain when to do the delete of the TLog backup files, if I want to only retain last week's, but I definitely MUST retain all of last week's TLog backup files otherwise they are totally useless right? If I acidentally set the delete task to delete the first TLog backup file after the Full backup, then the whole backup chain is useless, right??
Ugh, this seems complicated. Am I making this overly complicated? Or is it actually this complex and many people don't notice because they don't do restores very often? Has someone thought this all through and documented this stuff up?? Again, I am bonkers??? :hehe:
(actually, the real-world example I walked into is much more complicated than this!!)
August 25, 2011 at 4:17 pm
jpSQLDude (8/25/2011)
So there are two issues. Right at 12:05am when your Full backups start, there is no TLog backup happening. But the way I've seen it set up, the TLog backups Maintenence Plan is totally separate. So it starts trying to do TLog backups at 1:05am, and every hour after that. So I'm actually not sure what really happens here -- does the hourly TLog backup Maintenenace Plan start doing its thing only after a database has received this week's Full backup? Or do all TLog backups wait until after the Full backup Maintenence Plan has finished? Or does the first TLog backup Maintenence Plan sit there and wait for all the Full backups to finish, then all the intervening TLog backups are skipped???
Full and log backups can run concurrently with no issues
If I acidentally set the delete task to delete the first TLog backup file after the Full backup, then the whole backup chain is useless, right??
Yup, unless you have any differential backups in there anywhere.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2011 at 8:54 am
Thanks Gail, and I know there are different considerations for every server, but do you have a somewhat standard set of Backups and Deletes you do on a 'typical' server?
I don't know about you, but I've encountered many SQL instances where no one had ever set up any Maintenance Plan ever. So my 'standard' plan is Weekly Fulls, daily Diffs, and hourly TLogs, and then set deletes to retain a month if I can, or less if they have less space.
Just wondering if you have something more sophisticated, or a number of plans, etc. Thanks!
August 26, 2011 at 9:04 am
Depends on business requirements, available maintenance time, available disk space, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2011 at 9:14 am
you really only want to be looking at retaining 8 days on disk, the previous files should be on tape and go into the rotation pool. To keep a months worth on disk is just wasting disk space to be honest
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 26, 2011 at 9:31 am
Its a common problem. The clean up task is based on the lastwrite time, so if the duration of the backup fluctuates you can end up with extra backups.
We set out clean ups to delete files in hours. 2 days = 47 hours to cater for this.
Perry Whittle (8/26/2011)
you really only want to be looking at retaining 8 days on disk, the previous files should be on tape and go into the rotation pool. To keep a months worth on disk is just wasting disk space to be honest
Have to agree. If the files must be persisted on disk somewhere then it should be on a file server away from the precious database server.
August 26, 2011 at 10:44 am
my point is, you want them on disk long enough for them to be persisted to tape, after that delete them!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 29, 2011 at 10:56 am
Perry Whittle (8/26/2011)
my point is, you want them on disk long enough for them to be persisted to tape, after that delete them!
Tape? You guys are funny!
But seriously, most places I've worked do NOT take backups seriously. I have to do whatever I can just to get any SAN space for backups at all, and the few places where they have a tape backup team they usually do a very poor job -- I can't ever rely on them. Actually getting backups offsite or over to another data center or multiple copies -- is something I preach all the time, to almost no avail.
The only thing I really do is every single week I submit a report that includes a large paragraph explaining how the current backup strategy (all data and log and backup files on the same storage subsystem) is a catastrophe waiting to happen, so I have done a complete CYA. I'd probably still get blamed if they lose all their databases, though. :angry:
August 29, 2011 at 12:03 pm
jpSQLDude (8/29/2011)
Tape? You guys are funny!But seriously, most places I've worked do NOT take backups seriously.
you want your data on every media possible. I used to work for a software house years ago and a customer used to religiously backup to tape and store them on site in a fire proof safe. Well the inevitable happened, the factory burned down and fire proof safes, well they're only fire proof for so long. Luckily they had sent a copy of their data to us a couple of weeks before the fire so they only lost 2 weeks transactions, better than 25 years i suppose!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 30, 2011 at 7:35 am
jpSQLDude (8/29/2011)
Tape? You guys are funny!But seriously, most places I've worked do NOT take backups seriously.
Yes, tape. Dedicated server, dedicated backup SAN, automated tape library.
Plus we have off-site daily backups of small-volume, mission-critical data.
I like to keep a week's worth of SQL backups on disk, even after they've been written off daily to tape by an incremental tape backup job. It's just easier if I need to do a quick restore to recover some information someone accidentally deleted.
I don't use T-SQL to delete backup files. I have a VB script (no, not PowerShell, call me antiquated) that not only checks to see that each file's modified date is in a range OK for deletion but also verifies that the archive bit has been cleared, thus assuring me that the file has, in fact, gone off to tape. This last part is important for me, b/c I'm not the tape backup admin. and we had times when backups weren't running properly, but I wasn't notified. So if the archive bit is still set, the file won't be deleted, no matter how old the file is.
Also, you may want to purge old backup records from MSDB. No need for me to keep them older than a year.
Rich
August 30, 2011 at 8:00 am
Perry Whittle (8/29/2011)
you want your data on every media possible.
The current customer I work for has all their servers in a datacenter in another part of the country -- I have no access to the servers at all. And I would love to copy my backups somewhere, but... how can I put this delicately without saying who I work for... that would land me in jail! At this point it seems all I can do is warn them repeatedly that they are setting themselves up for failure, and keep my fingers crossed (and save a month's worth of backups locally to the server).
August 30, 2011 at 8:48 am
jpSQLDude (8/30/2011)
all I can do is warn them repeatedly that they are setting themselves up for failure, and keep my fingers crossed (and save a month's worth of backups locally to the server).
dude i feel your pain, and sympaphise 😉
Just cover your backside cos when the chips are down they'll try blame someone else!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 7, 2011 at 10:22 am
I have a related question. When I delete a full backup, I would like to delete all the files that are rendered useless once that full backup has been deleted: specifically, the differential and log backups that occur before the earliest full backup that is still on disk. Does anyone have a way of doing this (PowerShell script, perhaps)?
November 7, 2011 at 11:38 am
mfreeman 45141 (11/7/2011)
I have a related question. When I delete a full backup, I would like to delete all the files that are rendered useless once that full backup has been deleted: specifically, the differential and log backups that occur before the earliest full backup that is still on disk. Does anyone have a way of doing this (PowerShell script, perhaps)?
i use a simple VB script!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 7, 2011 at 11:55 am
I used to use VBScript as well. Here is a search on this site of a few scripts that might help: http://www.sqlservercentral.com/search/?q=delete+backups&t=s
You mentioned it, but be sure that you keep all log files since the earliest full you have. Never know when a full backup might get corrupt and you go back to the one before that one.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply