?
Solved

Does FULL and LOG backup sequence affect backup size?

Posted on 2010-11-16
14
Medium Priority
?
308 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 2
  • +1
14 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 34150208
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
ID: 34151229
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
ID: 34151271
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 6

Accepted Solution

by:
subhashpunia earned 1000 total points
ID: 34153615
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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 34153696
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
ID: 34156595
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
ID: 34157571
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 34157597
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
ID: 34158137
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
ID: 34158175
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
ID: 34158237
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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 34158610
>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
ID: 34160414
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
ID: 34160442
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

762 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