| Author |
Topic |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2004-08-13 : 12:10:31
|
| I just got this new hat called "part-time SQL DBA". So bear with me if my question sounds rudimental.Plan A: simple recovery model, complete backup weekly, differential backup hourly.Plan B: full recovery model, complete backup weekly, transaction log backup hourly.You can tell that I am confused with differential backup and transaction log backup. I know their definitions. I just want to find out in real world, what are the differences. By the way, the databases I am talking about are 10 and 40 GBs for OLTP/OLAP with 5 hours off-peak/downtime per day. During business hours, they will like to limit the possible disruption to within 2 hours. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-13 : 12:23:18
|
| Plan A looks good. For Plan B, I would recommend more frequent full backups, maybe once a day. That way if you ever have to do a restore to a point in time, you won't have to grab the full backup and a weeks worth of tlogs to do the restore. More frequent full backups give you a better starting point.Diffs are changes since last full and do not allow point in time recovery. Tlogs are changes since last tlog backup and allow you to do point in time recovery.Tara |
 |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2004-08-13 : 12:30:11
|
| To put an example on what Tara is saying. Let's say Plan B DB fails at 11PM Sun. (And your full backup happens at midnight on Sun.). To restore, you are going to restore The previous Sunday's Full backup and a whole load (Somewhere around 160+) transaction log backups. If you take a full every day, then the most you ever have to restore will be 23.For Plan A, All you ever have to restore is 2 backups. The latest full, and the latest diff.But you lose the point in time restore ability. (With Plan B, you could restore to 10:47pm if you want).-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2004-08-13 : 12:43:06
|
| Thank you Tara and Chad.So here is what I am going to try: Combining them into one:Full Recovery model, complete backup weekly, differential backup daily and transactional backup hourly.Any potential downside? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-13 : 12:46:44
|
| Yes that would work. I just don't ever do diffs. Most people don't. Full backups each day makes it easier when you need to restore. What we do due to the criticality of our data is full backups once per day and tlog backups every 15 minutes. But it just depends on your environments what the backup plan would be.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-15 : 08:03:42
|
| My view on Weekly Full and Daily Diffs (and hourly Trans) [which is what we do for pretty much all our databases] is based on a) how much hassle it is to get something back from tape and b) how much disk space you have available for backup history.If you decide that you MIGHT need to recover to any time in the last 7 days AND its more than 30 minutes to get something back from tape (in our case the servers are at an ISPs, they promise to get stuff back from tape within an hour or two, and I believe them, but its too long for me) then you want a backup history of 7 days on disk - note: you also need it on tape too in case something lands on the building or the server melts :-)So ideally you have loads of disk space and you do Full backup each night and Trans ever hour, or ten minutes.In my case I know I will be asked to save some disk space at some point in the future so I doSunday Full, Daily Diff and hourly/ten-minutely Trans.A restore is last Sunday's Full, latest Diff [optionally] then all Trans. backups since the Diff.Make sure you have a task for clearing down MSDB otherwise that gets out of hand, and the MS provided SProcs for clearing it down take forever once the DB gets big (advert:see Tara's blog for details!)Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-08-15 : 17:19:04
|
| Daily diffs are useful if you don't have space for lots of full backups or don't have time to run a daily full backup.There is a risk involved though - if the last full is currupt you have to go back to the previous weeks apply the last diff from that full then all the tr logs from then (probably a weeks worth) and hope that they all work.I wouldn't rely on diffs like this without always doing a test restore on the full backup when it is taken.==========================================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. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-16 : 00:49:37
|
| The only time I've used diffs like this was at a startup company where we had massive amounts of money and not space or money. :) We did exactly what you are talking about:Full - WeeklyDiff - DailyTran - HourlyTest like Nigel said. Also, don't use this if you have the money for a full everyday. It can save you a heart attack, cross my fingers stiuation, if you have a DR need arise.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
compare these two backup plan...