itnifl
asked on
SQL 2008 Differential backups and the transaction log
Does the transaction log get truncated when performing a differential backup in full recovery mode?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
yes
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Notice the first sentence from Paul's blog:
There's still a widely held misconception that when properly in the FULL or BULK_LOGGED recovery models that full or differential backups can truncate the log. No. It *NEVER* happens
That leaves only one type of backup that will truncate the log.
There's still a widely held misconception that when properly in the FULL or BULK_LOGGED recovery models that full or differential backups can truncate the log. No. It *NEVER* happens
That leaves only one type of backup that will truncate the log.
ASKER
So when I have full, differential and transaction logs backups, I could choose to ignore to restore the differential backup and only restore transaction log backups since the last full backup? But that would be a bigger administrative burden. I guess I would prefer to restore the full, then the differential backups and then in the end the transaction log backups that occured after the last differential?
>>I guess I would prefer to restore the full, then the differential backups and then in the end the transaction log backups that occured after the last differential?<<
Consider requesting that enhancement from the MS SQL Server development team. It is too late for "Denali", but it may be in time for the following version to be released circa 2018.
Consider requesting that enhancement from the MS SQL Server development team. It is too late for "Denali", but it may be in time for the following version to be released circa 2018.
ASKER
acperkins: So you are saying that I would only be able to restore the full backup and the all the transaction log backups? When would I want to restore the differential backups then?
>>So you are saying that I would only be able to restore the full backup and the all the transaction log backups?<<
I don't know I have never done it.
>>When would I want to restore the differential backups then? <<
Perhaps when you want to restore to when the differential backup was run.
I don't know I have never done it.
>>When would I want to restore the differential backups then? <<
Perhaps when you want to restore to when the differential backup was run.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Under the full recovery model or bulk-logged recovery model, the inactive part of the log cannot be truncated until all its log records have been captured in a log backup. This is needed to maintain the log chain—a series of log records having an unbroken sequence of log sequence numbers (LSNs). The log is truncated when you back up the transaction log, assuming the following conditions exist:
A checkpoint has occurred since the log was last backed up. A checkpoint is essential but not sufficient for truncating the log under the full recovery model or bulk-logged recovery model. After a checkpoint, the log remains intact at least until the next transaction log backup.
For more information, see Checkpoints and the Active Portion of the Log.
No other factor is preventing log transaction.
Generally, with regular backups, log space is regularly freed for future use. However, various factors, such as a long-running transaction, can temporarily prevent log truncation. For more information, see Factors That Can Delay Log Truncation.
The BACKUP LOG statement does not specify WITH COPY_ONLY.
Reference:-http://technet.microsoft.com/en-us/library/ms189085.aspx