Systems Engineering and RDBMS

Tail Log Backup

Posted by decipherinfosys on April 17, 2009

Tail log backups were introduced in SQL Server 2005 and apply to only those databases that are under a Full Recovery model or a Bulk Logged recovery model.   Here is the BOL entry:

In most cases, under the full or bulk-logged recovery models, SQL Server 2005 requires that you back up the tail of the log to capture the log records that have not yet been backed up. A log backup taken of the tail of the log just before a restore operation is called a tail-log backup.

SQL Server 2005 usually requires that you take a tail-log backup before you start to restore a database. The tail-log backup prevents work loss and keeps the log chain intact. When you are recovering a database to the point of a failure, the tail-log backup is the last backup of interest in the recovery plan. If you cannot back up the tail of the log, you can recover a database only to the end of the last backup that was created before the failure.

Not all restore scenarios require a tail-log backup. You do not have to have a tail-log backup if the recovery point is contained in an earlier log backup, or if you are moving or replacing (overwriting) the database. Also, if the log files are damaged and a tail-log backup cannot be created, you must restore the database without using a tail-log backup. Any transactions committed after the latest log backup are lost.

So, as you can see – the tail log backup essentially captures the records in the txn log since the last log back up.  This is needed for restoring to the point of failure.  And tail log backups capture the tail of the log even if the database is damaged or offline.  You use the “NORECOVERY” or “CONTINUE_AFTER_ERROR” in the BACKUP LOG command.   The BOL entry under the resources section shown below has the details.


  • BOL entry – here.

One Response to “Tail Log Backup”

  1. sql wildcard,sql wildcards,sql rollback,rollback sql,sql copy table,sql sum,sql mirroring,sum sql,sql cluster,sql server performance,truncate in sql,backup sql,backup sql database,backup sql server,sql performance,date functions in sql,sql over,trunc…

    […]Tail Log Backup « Systems Engineering and RDBMS[…]…

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: