Solved

Does FULL and LOG backup sequence affect backup size?

Posted on 2010-11-16
14
299 Views
Last Modified: 2012-05-10
This is an advanced question about SS 2005 and SS 2008 backups.  When backing up a database that has a FULL recovery model, I perform both a FULL and a LOG backup every day, once a day.   I want to understand any differences between doing the log backup first versus doing the full backup first.  Using N to represent the most recent backup and N-1 to represent the backup just prior to the most recent backup  ...

1. How will the sequence affect the size of the backup files?
2. Will either sequence prevent me from restoring the N log backup on top of the N-1 full backup (restored without recovery, of course) to roll the database forward to a specific point in time that is between the N-1 backup and the N backup?   In other words, will either sequence require that I apply both the N and N-1 log backups to a restored N-1 full backup?

3. Is there a difference in the amount of truncation in the log with either sequence?

4. Are there any other differences that I should be aware of?

0
Comment
Question by:LarryHennig
  • 8
  • 3
  • 2
  • +1
14 Comments
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
It depends!

Some detail first...when are you doing the full and log backup?  Why only doing 1 log backup a day?
0
 

Author Comment

by:LarryHennig
Comment Utility
I start backups at a point in time where there is little or no data activity.  The actual time varies by server.  I do not start backups between 1 and 3 AM to avoid DST switch-over issues.

I do once-a-day backups because that is mandated. This as a condition I cannot alter.  
0
 

Author Comment

by:LarryHennig
Comment Utility
Anticipating one of your considerations ... I do not have a problem of excessive log size on any instances.  If I encounter one, I will be allowed to perform log backups more frequently on that server.
0
 
LVL 6

Accepted Solution

by:
subhashpunia earned 250 total points
Comment Utility
1. How will the sequence affect the size of the backup files?
--No affect on size of bith type of backups.
2. Will either sequence prevent me from restoring the N log backup on top of the N-1 full backup (restored without recovery, of course) to roll the database forward to a specific point in time that is between the N-1 backup and the N backup?   In other words, will either sequence require that I apply both the N and N-1 log backups to a restored N-1 full backup?
--No difference in recovery point of view.
3. Is there a difference in the amount of truncation in the log with either sequence?
--No difference
4. Are there any other differences that I should be aware of?
--There is a little difference in the sequence of restore in case of recovery:
In case then you take backups FULL > TRAN then Tail Log the restore sequence would be: FULL > TRAN > TRAN (tail log)
In case then you take backups TRAN > FULL then Tail Log the restore sequence would be: FULL > TRAN (tail log)

For more details on restore sequence please check my post:

http://sqlreality.com/blog/ms-sql-server-2008/all-possible-restore-sequence/

0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
Comment Utility
1) yes, it does affect the size of the full backup size.
explanation: the FULL backup does also backup the active portion of the transaction log, so, if you do the log backup shortly before the full, then you will have a smaller full backup file than if you do the log backup shortly after the full.

0
 

Author Comment

by:LarryHennig
Comment Utility
angellll, To make sure I understand, please verify the accuracy of what I have written below and correct any inaccuracies.  As background, I read an article by Brent Ozar in which he wrote that the full backup includes all transactions completed during the full backup.  

If the transaction log is backed up first, the log is truncated. This reduces the active portion of the log to uncommitted transactions as of the start of the log backup (or is it as of the end of the log backup?).  Thus, the subsequent full backup will contain - of all transactions that remained in the log and all new transactions - those that were committed before the end of the full backup.  
0
 

Author Comment

by:LarryHennig
Comment Utility
subhashpunia:  Thanks for the info on the sequence difference.  I realize now that the log backup would contain transactions not in the full backup if the log backup was taken last.  Makes perfect sense.  

Is angelli right about the difference in size (please read her reason)?  If not, why not?


0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
So, the point of doing trans log backups is to be able to recover to a point in time.  This means that you really need to take more frequent log backups.  If you don't, then taking them at all is useless.  In which case you should switch to the simple recovery model.  
0
 

Author Comment

by:LarryHennig
Comment Utility
Chapmandew, please answer the questions asked and avoid prosthelytizing.  The once-a-day log backups are not useless. They provide a benefit - a second recovery path to the promised recovery point (of the end of the prior business day).  If the most current backup cannot be restored due to file corruption (perhaps as the server is restored from tape), then I can still restore the previous full backup and then restore the most current log.  I can also use the log backup to recover to a point in time prior to the most recent full backup (before an accidental deletion, for example). I will also probably be able to restore to a point in time after the most recent full backup if I start by taking a tail log backup of the live log.  So, you can see that, within a mandate that only allows once-a-day backups, the presence of the log backup stil provides additional safety.  

Given the number of SS gurus that warn DBAs that backups are occasionally unrestorable, this is a valid measure to take within the limits of my mandate.

OK, now I am getting off of my soapbox and we can continue with the question at hand, which I will refine in my next post.  (Sorry to "flame", but it is frustrating how many times someone responds to a new question with advice that does not answer the question).
0
 

Author Comment

by:LarryHennig
Comment Utility
angelll:  How much bigger will a full backup typically be if it is taken right after a log backup on a system that is having little activity?  

From the answers provided above and some additional reading about what the active portion of the log is, it seems that the answer is "it depends on how old the oldest uncommitted transaction is and how much activity has taken place since it last started."  So, it looks like I will have to do some experimenting to be certain, but assuming that there are no long-running transactions at the time I start the backups, and that there is little DB activity during the backup, would it be fair to say that the size difference would be minimal?
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
Its seems I didn't explain my point well...I apologize.  What you're describing is perfect for full backups and differential backups.  You can take differential backups throughout the day and they provide the same benefit as your scenario.  The difference?  You don't have to worry about maintaining your transaction log.  In full recovery, your transaction log file will continue to grow if it is not sized properly and you're not taking frequent log backups.  That is where I was going w/ the argument.
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
Comment Utility
>angelll:  How much bigger will a full backup typically be if it is taken right after a log backup on a system that is having little activity?  

little activity = little transaction log space, so only little space in the backup


>If you don't, then taking them at all is useless.
you can surely do 1 t-log backup per day, and still be able to recover to a point in time...
the only issue you have then is that if the system fully fails, you loose the full day (unless you have other means to "restore" the data

0
 

Author Comment

by:LarryHennig
Comment Utility
Thanks to all of you.  My understanding is deepened.  

Chapmandew, even though I saw your answer as off-topic, your concern that I be properly coached to do my "prime function" is appreciated.   My service level is indeed to "recover to the end of the last business day", and that is all I am allowed to prepare for.  While that may seem illogical, it was an informed business decision of our senior mangement and thus I cannot justify spending time on a higher service level.  

Your point is worth repeasting for others who are in a different situation - taking frequent log backups and moving them to another server are important things to do when you need to minimize data loss, and taking frequent log backups helps minimize log size/growth.
0
 

Author Closing Comment

by:LarryHennig
Comment Utility
Having multiple people respond provided a deeper response and corrected an minor error in the first response, which was the most thorough.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now