August 2, 2002 at 1:35 pm
Hi, I have a SQL 2000 environment. I set up a mainteneance plan to do daily backup at 10 PM, differential backup every 2 hours from 6 AM - 9:59 PM, and transaction log backup every 15 minutes throughout the day. My question is this: how should I set up my transaction log backups to have fewer backup files?
August 2, 2002 at 2:10 pm
15 mins isnt a bad number to use. How much can you data in minutes can you afford to lose? If you do the backups it takes up space. If you just want to consolidate you could either backup directly to tape, or use a disk device to keep all your logs backups in one file. I like having the individual files but nothing wrong with devices.
Does that help any?
Andy
August 2, 2002 at 2:17 pm
I think so, thanks.
So, for every hour of the day, I'll have 4 tran log backup files?
Am I missing something?
August 2, 2002 at 2:38 pm
I roll with logs every hour and once a day backups. We may shrink this down. However, it's a question of restore time. How quickly do you want to be back and what's the restore time of a full, a diff, and a few logs vs a full and a bunch of logs?
That's what you have to consider.
Right now, you will have to restore between 1 (if it fails after the full, before the first log) and 9 files (full, diff, 7 logs)
Steve Jones
August 2, 2002 at 2:44 pm
It's a very small database, the backups take seconds to complete. But we're expecting this to grow, and we need data loss to be minimal, and time wise, the shorter time possible.
August 2, 2002 at 2:46 pm
Roll with fulls every hour (or every 30 minutes) and then logs every minute or two.
Be sure you get these off the local server in case the server itself fails.
Steve Jones
August 2, 2002 at 3:05 pm
So, do I wanna have a seperate file for each backup(every 15 minutes?)?
thx
August 2, 2002 at 3:07 pm
Steve, you mentioned you do log backups every hour. Do you create a seperate file each time or do you overwrite the existing?
quote:
I roll with logs every hour and once a day backups. We may shrink this down. However, it's a question of restore time. How quickly do you want to be back and what's the restore time of a full, a diff, and a few logs vs a full and a bunch of logs?That's what you have to consider.
Right now, you will have to restore between 1 (if it fails after the full, before the first log) and 9 files (full, diff, 7 logs)
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
August 2, 2002 at 6:07 pm
You need to keep the log backups from the time of the last full backup.
Andy
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply