What are Tail-Log Backups and When You May Need Them

In this article, we explain the concept of tail-log backups and check out the scenarios where they may be needed while using MS SQL Server

Tail-Log Backups are used for capturing the log records for data, for which the user hasn’t created any back-ups to prevent the data from getting lost or deleted from the system and also to ensure that the log chain stays intact. It is recommended that the user backs-up his/her transaction log in its tail before starting a recovering program in his/her SQL Server Database. Also keep a tool that can fix mdf files handy.

Note: Users do not need to implement Tail-Log Backups in all scenarios. It is not required for the backup already containing a recovery point in its earlier log backup. It is also unnecessary in scenarios where the users are trying to replace or move their database.

Scenarios where Tail-Log Backups are needed while using MS SQL Server

These are some of the scenarios in which the user should ensure that he/she is using the Tail-Log Backups.

• If the users’ database is online on the cloud and they plan to perform a restoration operation on their database, they should begin by creating back up of the tail of their log. In order to prevent an error from occurring in their online database, the user should ensure to use … WITH NORECOVERY option along with syntax ‘BACKUP Transact-SQL’ statement.
• If the user has stored the database in an offline setting and it fails to start and the user needs to start a restoration process for the database, he/she should first ensure to create a back up of the tail of the log, to prevent his/her data from getting lost. This is recommended as no transaction occurs during this time. It’s optional for the users whether they want to use the WITH NORECOVERY option or not.
• In case the user’s database is corrupt or damaged, he/she can try to start a tail-log backup by simply using the ‘WITH CONTINUE_AFTER_ERROR’ option in their BACKUP statement.

Note: User can only succeed in backing up the tail of a log on a damaged database only if their log files are still undamaged. It is necessary for the database to be in a state that it can support tail-log backups, for the process to succeed. The database should also not contain any bulk-logged edits or changes. If the user is unable to create a tail-log backup then, any transactions that were committed after their latest log backup will be lost.

• BACKUP NORECOVERY Option – Users should use ‘NORECOVERY’ when they intend to continue or start a restoration operation on their database. NORECOVERY is used for taking the database to the state of restoring. This ensures that the database is not changing after initiating the tail-log backup. Note that users’ log will be truncated until he/she doesn’t specify these two options: COPY_ONLY option or NO_TRUNCATE option.
• CONTINUE_AFTER_ERROR – This syntax or option should only be used when the user wants to create a back up for the tail from a damaged database.

When a user creates back up of a tail of a log in a damaged database, some metadata that was ordinarily captured by the log backups might become unavailable.

 Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit www.datanumen.com