MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter

















Latest SQL Server Tips










TAIL-LOG Backup in SQL Server Step by Step Tutorial with Examples

Read Comments   |   Related Tutorials: SQL Server Database Backup Options > Next Topic: Performance Tuning

Page 16 / 16


Click here to read this tutorial from the beginning...

Introduction

A tail-log backup captures any log records which has not yet been backed up i.e., the tail of the transactional log to prevent any work loss and to keep the transactional log chain intact. Before you can recover a SQL Server database to its latest point in time, one must back up the tail of its transaction log without fail. Note that the tail log backup will be the last backup of which one can take to recovery the database.

Scenarios when you need to perform a TAIL LOG backup

Microsoft recommends you to Perform a TAIL LOG backup during the following scenario:-

  • The database is currently ONLINE and you are planning to perform the restore operation on the database then begin by backing up the tail of the log. To avoid any error for an ONLINE database, you must use the … WITH NORECOVERY option of the BACKUP Transact-SQL statement.
  • If a database is OFFLINE and fails to start and you need to restore the database, first back up the tail of the transaction log file. Because no transactions can occur during such time, it is optional to use WITH NORECOVERY during such times.
  • If a database is DAMAGED, then try to take a tail-log backup by using WITH CONTINUE_AFTER_ERROR option of the BACKUP statement.

Note: On a damaged database backing up the tail of the log can only succeed if the log files are undamaged, and the database is in a state that supports tail-log backups, and at the same time the database does not contain any bulk-logged changes. If you are unable to create a tail-log backup, any transactions committed after the latest log backup will be lost.

Difference between BACKUP NORECOVERY and CONTINUE_AFTER_ERROR options

NORECOVERY: When you specify NORECOVERY option before you take the log backup then you intend to continue with a database restore operation after the successful backup of transaction log file. NORECOVERY option takes the database into the restoring state. This will guarantee that the database does not change after the tail-log backup is completed. The transaction log is truncated unless you specify NO_TRUNCATE option or COPY_ONLY option while running transaction log backups.

Create a TAIL LOG backup WITH NORECOVERY Using TSQL command

BACKUP LOG [MyTechMantra] TO DISK = 'C:\DBBackups\MyTechMantra_NORECOVERY.TRN' WITH NORECOVERY, COMPRESSION, CHECKSUM, STATS = 25

Create a TAIL LOG backup WITH NO_TRUNCATE when Database is Damaged Using TSQL command

Note: Microsoft recommends that you avoid using NO_TRUNCATE, except when the database is damaged.

BACKUP LOG [MyTechMantra]
TO DISK = 'C:\DBBackups\MyTechMantra_NO_TRUNCATE.TRN'
WITH NO_TRUNCATE, COMPRESSION, CHECKSUM, STATS = 25



CONTINUE_AFTER_ERROR : You must specify CONTINUE_AFTER_ERROR option while backing up transactional log file only if you are backing up the tail of a DAMAGED SQL Server Database.


Create a TAIL LOG backup WITH CONTINUE_AFTER_ERROR Using TSQL command

BACKUP LOG [MyTechMantra]
TO DISK = 'C:\DBBackups\MyTechMantra_NORECOVERY.TRN'
WITH NORECOVERY, CONTINUE_AFTER_ERROR, COMPRESSION, CHECKSUM, STATS = 25


Clicking Next Page button to continue reading the topics and click on the Previous Page button to revisit the previous topic.

Previous Page.. Next Page..


Learn More...




Last Updated On: Feb 12, 2014



Share this Article



Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips




Please leave your Valuable Comment or Let us know how this article helped you: