Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In full recovery model, if we take a full backup, the log file is not truncated. It is done when we take a transaction log backup.

Any particular reason of why it is done this way?

share|improve this question
1  
If full backup would truncate the log, how would log shipping work? –  Remus Rusanu Sep 21 '12 at 11:21
add comment

2 Answers 2

Being in the full recovery model means you want the option of restoring to any point in time. That means having a full set of transaction log backups after any database backup.

Imagine you have a db backup from midnight yesterday and log backups from 6, 12 and 18:00. Now you take another db backup. If the log is truncated, you can't restore to 11pm (which would require the previous db backup and all the log backups since).

share|improve this answer
add comment

Only a log backup clears out the inactive portion of the log. The whole point of being in full recovery is to be able to restore a full backup and subsequent log backups so you can restore to a specified point in time. If a full backup cleared out the log (especially if you're not taking log backups), how would you do that?

If all you care about is full backups, and you're okay with only ever being able to recover to the point of the last full backup, then do yourself a favor: switch to simple recovery model. This will make sure the log manages itself more effectively than if you are in full recovery model and don't bother taking log backups (in which case the log will not clear out). If you do care about point-in-time-recovery, then stay in full and schedule log backups at a frequency that makes sense for your business (every 15 minutes, once an hour, etc.).

As an aside, be careful with the word "truncate." Many people associate that with shrinking. Taking a full backup will never do anything with the log file. And taking a log backup clears out the inactive portion, but it does not shrink the file (which many people expect).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.