Does FULL and LOG backup sequence affect backup size?
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?
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?
ASKER
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.
I do once-a-day backups because that is mandated. This as a condition I cannot alter.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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?
Is angelli right about the difference in size (please read her reason)? If not, why not?
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.
ASKER
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).
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).
ASKER
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?
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?
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
Having multiple people respond provided a deeper response and corrected an minor error in the first response, which was the most thorough.
Some detail first...when are you doing the full and log backup? Why only doing 1 log backup a day?