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?

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

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).

| improve this answer | | | | |

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).

| improve this answer | | | | |

In the full recovery model SQL Server does not truncate committed transactions until they have been backed up. It allows creating full, differential and transaction log backups thus making point-in-time recovery possible.

For example: We have full database backup every 12 hours and log backups every 2 hours. Look at the picture below.

enter image description here

Assume we have deleted a the crucial data at 21:00. what shall we do? We can restore our database to 20:59 statement. The transaction log backup that was made at 22:00 allows to roll back to 20:59. In this case you will lose all data from 21:00 to 00:00.

RESTORE LOG your_database FROM DISK = 'c:/your_database_log_14_00.bak' WITH 
RESTORE LOG your_database FROM DISK = 'c:/your_database_log_16_00.bak' WITH NORECOVERY
RESTORE LOG your_database FROM DISK = 'c:/your_database_log_18_00.bak' WITH NORECOVERY
RESTORE LOG your_database FROM DISK = 'c:/your_database_log_20_00.bak' WITH NORECOVERY
RESTORE LOG your_database FROM DISK = 'c:/your_database_log_22_00.bak' WITH STOPAT = '2015-11-20 20:59:59.000', RECOVERY

Using log backups you can keep tour data safe and sound. Here are some tips when the full recovery model is better to use:

  1. There is a big necessity to recover all data
  2. The database contains some filegroups and there is the need to restore of read/write secondary filegroups and, optionally, read-only filegroups one by one
  3. There is a necessity of point-in-time recovery
  4. There is a necessity of individual pages restore


  1. If there is no necessity to make backups of the transaction logs
  2. If there is no necessity in point-in-time recovery
  3. If it is acceptable to lose some data
  4. If database changes are infrequent

Use simple recovery model. In simple recovery model when a checkpoint occurs all committed transactions are removed from transaction log automatically.

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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