| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2004-07-12 : 11:50:09
|
| Our transaction logs are recorded every 15 minutes. Full weekly backups and nightly incrimental backups are performed.The transaction logs aren't deleted until I do it manually. Anyone have a scheduled proc that'll delete the transaction logs older than some specified time?Sam |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-07-12 : 12:21:32
|
| seehttp://www.nigelrivett.net/BackupAllDatabases.htmlYou can just use the file handling bit if you wish.You can also get the file datetime if you don't have it in the name.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-12 : 14:01:45
|
| I store the filenames, and "expiry" dates, in a table and use that as the basis for a delete. If the DELETE fails (i.e. file not found) I don't care.This allows me to have different delete policies for different files. For example, no pont in keeping TRANS files from yesterday on the disk if the FULL BACKUP, from last Sunday, that they reply on has been deleted.A round-robin basis of deleting everything older than, say, two days is going to kill off your FULL backups long before the DIFFs and TRANs that depend on them ...Kristen |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-07-12 : 14:24:30
|
Thanks Nigel...quote:
Originally posted by Kristen
A round-robin basis of deleting everything older than, say, two days is going to kill off your FULL backups long before the DIFFs and TRANs that depend on them ...
Can you elabortate on this. Pretend you're talking to an idiot. Wait! I am an idiot... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-13 : 01:51:00
|
| Dear Mr Idiot, Sir!Some of the maintenance routines I've seen (including Tara's blog, I think [although she doesn't have Diff backups to worruy about]) delete all backups files in the "target" folder after, say, two days. That's what I meant by Round Robin - its a First In First Out system, you get kicked out after two days.However, lets assume you make a FULL backup on Sunday, Differential backups each night at, say, 1AM, and Transaction backups every hour.On Saturday at 3:15pm you need to restore to Saturday 3pm state (you just deleted a couple of million rows by mistake watching the ball game out of the corner of your eye ...)So you need Sunday's full backup, Saturday 1am's Differential backup, and 2am, 3am ... 3pm transaciton backups.Deleting ALL files that where more than 2 days old would mean that you don't have the Full backup from last Sunday. (But you have it on tape, I'm absolutely sure of that, oh YES!) Its gonna be a hassle getting it back from tape. Tape is for when the server catches fire ...So here we set a retention time for each backup, and store the filename in a table. Then each hour we delete anything past its expiry date. Probably the shortest you would want to keep them (assuming the backup strategy above) is Full backups for 7 days, differential backups for 2~3 days and transaction backups for 2 days.Kristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-07-23 : 05:25:47
|
If you're backing up the logfile with a tsql, you can use the with expiredate or retaindays option and set to how many days before the file will be overwritten. I hope this helps.quote:
Originally posted by SamC
Our transaction logs are recorded every 15 minutes. Full weekly backups and nightly incrimental backups are performed.The transaction logs aren't deleted until I do it manually. Anyone have a scheduled proc that'll delete the transaction logs older than some specified time?Sam
|
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-23 : 06:08:21
|
| That only works if you are backing up the the same file each time doesn't it? (Which I have to say I would be a bit nervous to do!)Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-07-25 : 18:14:18
|
| I put the datetime of the backup in the file name and use that for deletes.full, diff and log I usually bcakup to different directories and possible treat them with different delete schedules.Not sure if that link I posted has all that as it was written a long time ago.Remember to run test restores as you don't knkow if you have a backup until you have restored it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-07-25 : 18:22:29
|
quote:
Originally posted by KristenThis allows me to have different delete policies for different files. For example, no pont in keeping TRANS files from yesterday on the disk if the FULL BACKUP, from last Sunday, that they reply on has been deleted.
huh? tlogs do not depend on any specific full backup. If you could pull a full backup from last december, and had all the tlogs from then till now, you could perform a full recovery.Now it might make more sense to try and recover from yesterday's full backup, but whatever..-ec |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-07-25 : 21:38:28
|
| eyechartThe point is that to apply the log backups you need a prior full backup.If your only full backup on disk is from yesterday then to apply prior logs you will need to retrieve the previous full backup from tape (or wherever) so there is no point having those logs on disk (well maybe to save time) - you might as well get them from tape too.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-07-25 : 22:26:17
|
| Nigel, I understand how the recovery process works, i just didn't quite understand Kristen's description of it.I prefer Tara's method of keeping backups that are x days old online. This allows you to perform a recovery to any point in time given that window. Outside that window, you need to retrieve from tape.If you have different on-disk retention periods for full, diff and tlog then you may not be able to perform a point in time recovery because the tlogs are not on disk. Or, you may have to go to tape to retrieve the missing files.Apparently, everyone has crappy tape subsystems here and are deathly afraid of pulling data from it ;) -ec |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-07-25 : 22:47:11
|
| If you understand then you should look at Kristen's post again. She is just saying that there is no point in keeping log backups that were taken before the earliest backup.i.e.This allows me to have different delete policies for different files. For example, no pont in keeping TRANS files from yesterday on the disk if the FULL BACKUP, from last Sunday, that they reply on has been deleted.Sure you can restore the logs from a backup taken 2 years ago (maybe) but if you only have a full backup from yesterday then there's no point in keeping the previous days logs.She was just pointing out that if you cotinually delete files that are a week old then you will be keeping log files that cannot be restored and you may as well delete all those up to the next full backup (and differentials too).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-07-25 : 23:03:16
|
quote:
Originally posted by nr
If you understand then you should look at Kristen's post again. She is just saying
errr, "He".Damian |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-26 : 01:29:04
|
quote:
Originally posted by eyecharthuh? tlogs do not depend on any specific full backup. If you could pull a full backup from last december, and had all the tlogs from then till now, you could perform a full recovery.
Indeed ... what I had in mind, but no doubt failed to explain and Nigel has clarified, was that personally I like to keep, on disk, a set of backups that enable me to do a RESTORE without having to resort to TAPE. So that would be the last full backup, the last differential backup (if used) and the transaction logs since then.If I am doing Differential backups I can delete the TLogs that are older than the most recent Diff. But I don't want to delete the Full backup until I have taken another one, otherwise in order to do a restore I will have to get Systems folk out of bed, and they will have to find the right tape, get it mounted, get the stufff of it, etc. before I can begin the restore.Kristen |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-07-26 : 02:28:21
|
quote:
Originally posted by KristenIndeed ... what I had in mind, but no doubt failed to explain and Nigel has clarified, was that personally I like to keep, on disk, a set of backups that enable me to do a RESTORE without having to resort to TAPE. So that would be the last full backup, the last differential backup (if used) and the transaction logs since then.
yes, it is now clear to me. However, my point was that this approach only benefits performing a full recovery.If you have to perform a point in time recovery (to a time preceeding the last diff on disk), then you will have to go to tape. That is why I like to take Tara's approach and get rid of all backups to a certain date.I have had to perform many recoveries like this in the last few years. You know the kind, where you have to recover to a copy of your database to a certain point in time just to get to a table that was dropped, or to some data that was truncated. -ec |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-26 : 03:37:45
|
I figure we will know about it within a week, or its a full forensics job ("how the heck did someone buy all this stuff over the last year without paying for it?" )So we do Sunday Full, Daily Diff and Hourly/TenMinute Trans. (depending on the business need). The idea of using the Diff's is that we can get more backup history for a given disk space. We keep the Trans. for a couple of days, Fulls, and their corresponding Diffs, for two weeks - so point-in-time restore is good for a couple of days, which in my experience is when 99% of "oh-my-God" discoveries are made!All my apps our web based these days, and 90% of those at an ISP. So the thought of trying to get them to "hurry" to restore a file from tape is too scary to contemplate! Equally we are unlikely to restore to point-in-time that was very long ago - we'd lose too much in terms of online orders etc., so for something like that we would have to restore a "sandpit" copy of the data from tape, work out what was lost and re-merge that data with the current data.So I figure that point in time is only likely to be needed [in an emergency situation] for the very recent past, but we might well want to restore yesterdays backup, or sometime in the past week or two, to a fresh DB to do some sort of comparison.Kristen |
|
|
|
|
Automatic removal of transaction logs