| Author |
Topic |
|
kpgraci
Yak Posting Veteran
68 Posts |
 Posted - 2011-07-06 : 15:50:46
|
| I am a Programmer/Sql Server Novice trying to develop a backup/restore plan.I know that a full backup must be done first, and that a differential backup saves only changes whereas the transactional log backup saves the transaction logs - either of these combined with the full backup and allows you to restore a database.I'm familiar with the differential backup concept (saving only changes), and I'm assuming the SQL server transaction logs are basically a list of commands requested of sql server to perform, so re-processing them will get the database back to whatever state the log backup was made.So why are both differential and transaction logs backups supported and which is better in what circumstances?It seems to me, while they use very different techniques, they accomplish the same thing, storing enough info to turn an old full backup into a restored database.Insights?kpg |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-07-06 : 16:02:49
|
| Transaction log backups give you point-in-time recovery. Differentials do not. A differential is only the full backup plus whatever changes have happened since the full backup and up to the differential. They do not include any changes after the differential. But a tlog backup does.Here's an example:12am full backup12pm diff12am-11:59pm - tlog backups every 15 minutesSystem crash at 6:01pm that requires complete recovery.You would restore the 12am full backup, the 12pm diff, and then all of the tlogs from 12pm until 6pm. If you were able to do a tail backup at 6:01pm or after, then you could even restore that.If you don't do tlog backups, then you could only restore full backup + diff, which would mean data loss from 12pm until 6pm-ish. Hope this makes sense. Ask more questions if needed!Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-06 : 16:36:15
|
| Here's another example. 12am full backup12pm diff12am-11:59pm - tlog backups every 15 minutesAt 11:25 (35 minutes before the diff) a careless user deletes some data from a very busy, very critical table. You need to get it back.You restore the full (as a new database so that you can copy the data over).You can't restore the diff, because that will restore the DB to how it was after the data delete. So restore the transaction logs up until the 11:15 one, then the 11:30 one you restore WITH STOPAT 11:24:30 (just before the data delete)You can't do that with a diff. A diff is an all or nothing. Log backups can be restored to a point in time within the log backup.--Gail ShawSQL Server MVP |
|
|
|
vihanga
Starting Member
1 Post |
Posted - 2012-05-07 : 13:15:52
|
| Hi,thanks for the info.If I have like below ..12:00 am - Full 3:00 am - Trans.log6:00 am - Trans log 8:00 am - Diff.9:00 - Tranlog :::If my DB crashes at 9:30 am what is the aproach to restore ?. Would transactional backups between full-Diff or diff-Diff be required to restore ?. or Diff after Full have all the required data for that period ?. Please advise . Our current strategy is full every day once and Diff. for every 2 hours. Sometimes have to truncate t.log(after backing it up) due to space issues. I want to figure out does diff. backup rely on previous transactional backup or we only need trans.log.backup after the full/diff ?. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Jheim
Starting Member
4 Posts |
Posted - 2012-07-18 : 10:59:50
|
| Some of the answers pre-suppose a certain "stereo-type" of tlog vs diff usage. To follow up on the original question....here are two competing scenarios TLOG vs DIFF. Please feedback.SCENARIO #1FULL: 12amTLOG: every 15 minutes (:00,:15,:30,:45)SCENARIO #2FULL: 12amDIFF: every 15 minutes (:00,:15,:30,:45)Three thoughts before you discuss.* I know that if you wanted to recover to an exact point in time say 7:53...you could do that with TLOGs and with DIFF you would only be able to get recovery to :00,:15,:30,:45. Point for TLOGs.* Doesn't the DIFF scenario allow you to use Object recovery tools (like Redgate's) to recover objects more precisely? Point for DIFF.* Further, If the previous point were not an issue, wouldn't the DIFF scenario allow you to have the DBs in SIMPLE recovery (carefree log maintenance)? POINT DIFF.* TLOGs allow for Transaction Log mining of exactly who/what/when investigation (if you have the patience for it!). Point TLOG.* TLOGs fit with other SQL Server mechanisms like log shipping better. Point TLOG.Anything else about TLOG and DIFF differences that make the "unusual" DIFF scenario a possibility or not?Personally I go with TLOG Scenario but I wonder if DIFFs done often would fit our DR requirements (<30min data loss) rather than TLOG since we rarely if ever care about an exact point in time restore??? Old Dog trying to learn New Tricks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-07-18 : 12:43:23
|
| And what is so bad about full recovery model? Your tlog is still going to need to be the size of the largest transaction if you are using simple recovery model. Why limit yourself to less recovery points when you aren't really getting a benefit from simple recovery model? It's not like it's giving you a performance boost. And the tlog size should generally be about the same size regardless of recovery model if you are doing your tlog backups frequent enough.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Jheim
Starting Member
4 Posts |
Posted - 2012-07-19 : 10:20:22
|
| Three things Tara,* thanks for the comment about Diffs being size since last Full, not incremental...thats the type of point I was looking for.* Diffs are not necessarily needed. FULL and TLOG are fine if increments fit your Recovery Plan needs.so I disagree with:"A proper strategy should have full backups, differential backups, AND transaction log backups. All 3."* "bad" about FULL, gosh, thats all I use if I can help it. Not bad.As far as SIMPLE vs FULL...the key point about SIMPLE you state yourself: "....if you are doing your tlog backups frequent enough." ... that "IF" can be an issue...with SIMPLE you just dont have to care about that job or process. Again, don't reccommend SIMPLE, just that was my point.So thanks for that point on Diff Size.Old Dog trying to learn New Tricks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-07-19 : 12:37:34
|
quote:
Originally posted by Jheim
* Diffs are not necessarily needed. FULL and TLOG are fine if increments fit your Recovery Plan needs.so I disagree with:"A proper strategy should have full backups, differential backups, AND transaction log backups. All 3."
Diffs should be in a backup plan due to recovery time. If you are backing up the tlog every 15 minutes and need to restore to a time say an hour before the next full backup ran, you've got a TON of tlogs to roll through. So your recovery time is going to be slower than if you have a diff say 12 hours after the full. Recovery time is very important to availability requirements.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-07-19 : 12:39:51
|
quote:
Originally posted by Jheim
* "bad" about FULL, gosh, thats all I use if I can help it. Not bad.As far as SIMPLE vs FULL...the key point about SIMPLE you state yourself: "....if you are doing your tlog backups frequent enough." ... that "IF" can be an issue...with SIMPLE you just dont have to care about that job or process. Again, don't reccommend SIMPLE, just that was my point.
"IF" shouldn't be a problem for a DBA as it's our number one job to have the right plan in place and ensure it meets the needs of the application/business/database. I always recommend a starting point of 15-minute tlog backups if PIT recovery is needed. Increase the frequency as needed. Some systems may need tlog backups every 5 minutes, some can get away with every 30 minutes. It just depends on the environment, and it's the DBA's responsibility to figure it out.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Jheim
Starting Member
4 Posts |
Posted - 2012-07-19 : 14:08:39
|
| Well, first note: diffs not essential...yo're simply stating how they are used and how they can help...sure...I know that. but to say to someone that diffs should be part of ones backup plan is not correct. maybe you mean they should be "considered"...and "understood". I've got a few servers that I don't use diffs and they're fine.on the second note: Couldn't agree more. FULL/15-minutes TLOGs is what I do normally. I've had an every 5 tlog as well...nothing new there.What I'm saying is not that TLOG backups are a problem...certainly not. its bread-n-butter DBA-ing. DUH!The point originally was that SIMPLE recovery allows you not to think about tlog backups at all. Its possible that a paticular system might do just fine with SIMPLE. Yes, figure it out.I suppose I shouldn't contend with the "Almighty SQL Goddess".you get the last lightning bolt :) ...done here.Old Dog trying to learn New Tricks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-07-19 : 14:29:36
|
quote:
Originally posted by Jheim
Well, first note: diffs not essential...yo're simply stating how they are used and how they can help...sure...I know that. but to say to someone that diffs should be part of ones backup plan is not correct. maybe you mean they should be "considered"...and "understood". I've got a few servers that I don't use diffs and they're fine.
You only need them at the time of recovery. Of course your system is fine without them as you aren't in a crash situation having to do a restore. I definitely have production systems without diffs in place, but all of my critical systems where uptime requirements are high and very little data loss is expected have diffs in place.And my title doesn't mean we can't have a healthy debate. It's part of the learning experience. I certainly am not an expert and don't pretend to be. I answer questions here not only to help the community but also to learn. I've certainly posted things that were not correct and have learned from it. I'm open to other's views as well. Hopefully others can chime in on this topic so that we can continue a discussion. It's nice to have more views on a topic, and I'd love to learn more.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Jheim
Starting Member
4 Posts |
Posted - 2012-07-20 : 09:32:54
|
| Sorry, i was a bit defensive.Old Dog trying to learn New Tricks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
atilla
Starting Member
1 Post |
Posted - 2013-07-31 : 13:25:49
|
quote:
Originally posted by tkizer
Transaction log backups give you point-in-time recovery. Differentials do not. A differential is only the full backup plus whatever changes have happened since the full backup and up to the differential. They do not include any changes after the differential. But a tlog backup does.Here's an example:12am full backup12pm diff12am-11:59pm - tlog backups every 15 minutesSystem crash at 6:01pm that requires complete recovery.You would restore the 12am full backup, the 12pm diff, and then all of the tlogs from 12pm until 6pm. If you were able to do a tail backup at 6:01pm or after, then you could even restore that.If you don't do tlog backups, then you could only restore full backup + diff, which would mean data loss from 12pm until 6pm-ish. Hope this makes sense. Ask more questions if needed!Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
What of if he had done differential backup by 5:59pmis it not going to be same as transactional backup at 5:59pm |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-07-31 : 13:30:41
|
quote:
Originally posted by atilla
quote:
Originally posted by tkizer
Transaction log backups give you point-in-time recovery. Differentials do not. A differential is only the full backup plus whatever changes have happened since the full backup and up to the differential. They do not include any changes after the differential. But a tlog backup does.Here's an example:12am full backup12pm diff12am-11:59pm - tlog backups every 15 minutesSystem crash at 6:01pm that requires complete recovery.You would restore the 12am full backup, the 12pm diff, and then all of the tlogs from 12pm until 6pm. If you were able to do a tail backup at 6:01pm or after, then you could even restore that.If you don't do tlog backups, then you could only restore full backup + diff, which would mean data loss from 12pm until 6pm-ish. Hope this makes sense. Ask more questions if needed!Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
What of if he had done differential backup by 5:59pmis it not going to be same as transactional backup at 5:59pm
It depends. There could be some data differences in there, depends on the activity of the system.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
Differential vs Transaction Log backup
Tara Kizer