Transaction Log Backup

Transaction Log Backup
In Full or Bulk Logged recovery models, it is very important that we have scheduled periodic Transaction Log backups so it will help us to maintain the the size of the transaction log within reasonable limits and will allow for the recovery of data with the least amount of data loss in case of any failure.

Transaction Log backups come in three forms:

Pure Log Backup: —A Pure Log backup contains only transactions and is completed when the database is in Full recovery model or Bulk Logged recovery model, but no bulk operations have been executed. In case of Bulk Logged recovery Bulk Operations are minimally logged.

Bulk Log Backup: —Bulk Log backups contain both transactional data and any physical extents modified by bulk operations while the database was in Bulk Logged recovery.

Tail Log Backup: —Tail Log backups are completed when the database is in Full or Bulk Logged recovery prior to a database restoration to capture all transaction log records that have not yet been backed up. It is possible in some instances to execute a Tail Log backup even if the database is damaged.

Pure or Bulk Log Backup Example
BACKUP LOG SQLDBPool
TO DISK = 'D:\SQLBackups\SQLDBPool.TRN'

Tail Log Backup Example
BACKUP LOG SQLDBPool
TO DISK = ‘D:\SQLBackups\SQLDBPoolTailLog.TRN’
WITH NO_TRUNCATE