Sunday, July 31, 2011

Tail-Log Backups

Tail-Log Backups are needed when you wish to perform a database recovery on a database with full or bulk-logged recovery model and you are recovering to a point after the last full, differential, or transaction log backup.  If you are doing a recovery to point a in time prior to the last full, differential, or transaction log backup then a tail log backup is NOT required.

A Tail-Log Backup is also the first step in performing a manual failover of a primary log shipping server to a secondary server. 

A Tail-Log backup is simply a log backup taken using the BACKUP LOG statement.  It is called a Tail-Log backup because it is backing up the remainder or tail of the transaction log. What differentiates a Tail-Log backup from any other log backup?  When taking a Tail-Log backup, you need to ensure that transactions are not occurring after you do the backup.

If the database you are recovering is not damaged, online and available for connections, you should use the backup statement with NORECOVERY option.  Using the NORECOVERY option puts the database into the restoring state where no changes are allowed:
BACKUP LOG <database_name> TO <backup_device>
    WITH NORECOVERY 

If the database is damaged, you should use the CONTINUE_AFTER_ERROR option: 
BACKUP LOG <database_name> TO <backup_device>
    WITH CONTINUE_AFTER_ERROR
This option allows you to successfully produce a transaction log backup even if the database file is damaged.  Per the MSDN article, the log backup will succeed if:  "the log files are undamaged, the database is in a state that supports tail-log backups, and the database does not contain any bulk-logged changes".

No comments:

Post a Comment