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

Advertisements

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s