TAIL-LOG Backup in SQL Server Step by Step Tutorial with Examples
Related Tutorials: SQL Server Database Backup Options>
Next Topic: Performance Tuning
Page 16 / 16
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
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.
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
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.
- Do Signup for Our NewsLetter and Like Us on Facebook.
- How to Create Full Database Backups in SQL Server Using SQL Server Management Studio
- Understanding SQL Server Recovery Model In Depth
- How to Repair Suspect Database in SQL Server
- Different Types of SQL Server Backups
Last Updated On: Feb 12, 2014
Please leave your Valuable Comment or Let us know how this article helped you: