Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Differential vs Transaction Log backup
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kpgraci
Yak Posting Veteran

68 Posts

Posted - 07/06/2011 :  15:50:46  Show Profile  Reply with Quote
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

USA
38200 Posts

Posted - 07/06/2011 :  16:02:49  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 backup
12pm diff
12am-11:59pm - tlog backups every 15 minutes

System 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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 07/06/2011 :  16:36:15  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Here's another example.

12am full backup
12pm diff
12am-11:59pm - tlog backups every 15 minutes

At 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 Shaw
SQL Server MVP

Edited by - GilaMonster on 07/06/2011 16:37:08
Go to Top of Page

vihanga
Starting Member

1 Posts

Posted - 05/07/2012 :  13:15:52  Show Profile  Reply with Quote
Hi,

thanks for the info.

If I have like below ..

12:00 am - Full

3:00 am - Trans.log

6: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 ?.


Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 05/07/2012 :  13:33:45  Show Profile  Visit tkizer's Homepage  Reply with Quote
For a 9:30am crash, you would use the 12am full, the 8am diff and then the 9am tlog backup.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 05/07/2012 :  13:34:25  Show Profile  Visit tkizer's Homepage  Reply with Quote
Differentials every 2 hours doesn't sound like the right strategy is in place. How often are you doing transaction log backups?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Jheim
Starting Member

4 Posts

Posted - 07/18/2012 :  10:59:50  Show Profile  Reply with Quote
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 #1
FULL: 12am
TLOG: every 15 minutes (:00,:15,:30,:45)

SCENARIO #2
FULL: 12am
DIFF: 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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 07/18/2012 :  12:25:52  Show Profile  Visit tkizer's Homepage  Reply with Quote
You're going to need a whole bunch of storage for those diff backups. They are cumulative since the last full backup. The further away from the full backup it is, the longer recovery is going to take.

A proper strategy should have full backups, differential backups, AND transaction log backups. All 3.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 07/18/2012 :  12:43:23  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Jheim
Starting Member

4 Posts

Posted - 07/19/2012 :  10:20:22  Show Profile  Reply with Quote
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

Edited by - Jheim on 07/19/2012 10:23:40
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 07/19/2012 :  12:37:34  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 07/19/2012 :  12:39:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Jheim
Starting Member

4 Posts

Posted - 07/19/2012 :  14:08:39  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 07/19/2012 :  14:29:36  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Jheim
Starting Member

4 Posts

Posted - 07/20/2012 :  09:32:54  Show Profile  Reply with Quote
Sorry, i was a bit defensive.

Old Dog trying to learn New Tricks
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 07/20/2012 :  12:24:38  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by Jheim

Sorry, i was a bit defensive.






Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

atilla
Starting Member

1 Posts

Posted - 07/31/2013 :  13:25:49  Show Profile  Reply with Quote
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 backup
12pm diff
12am-11:59pm - tlog backups every 15 minutes

System 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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog



What of if he had done differential backup by 5:59pm

is it not going to be same as transactional backup at 5:59pm
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 07/31/2013 :  13:30:41  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 backup
12pm diff
12am-11:59pm - tlog backups every 15 minutes

System 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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog



What of if he had done differential backup by 5:59pm

is 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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000