Skip to main content

Tail Log Backups

Tail log backups capture the tail of the log even if the database is offline, damaged, or missing data files. This might cause incomplete metadata from the restore information commands and msdb. However, only the metadata is incomplete; the captured log is complete and usable.

 If a tail-log backup has incomplete metadata, in the backupset table, has_incomplete_metadata is set to 1. Also, in the output of RESTORE HEADERONLY, HasIncompleteMetadata is set to 1.

 To create a tail log backup, use below script

 BACKUP LOG [DB Name]
   TO [TLog_Device_Location]  
   WITH NO_TRUNCATE;  

 GO

 Scenarios That Require a Tail-Log Backup
  • If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log. To avoid an 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 log. Because no transactions can occur at this time, using the WITH NORECOVERY is optional.
  • If a database is damaged, try to take a tail-log backup by using the WITH CONTINUE_AFTER_ERROR option of the BACKUP statement.
On a damaged database backing up the tail of the log can succeed only 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. If a tail-log backup cannot be created, any transactions committed after the latest log backup are lost.

 The following table summarizes the BACKUP NORECOVERY and CONTINUE_AFTER_ERROR options.

 BACKUP LOG option Comments

 Use NORECOVERY whenever you intend to continue with a restore operation on the database. NORECOVERY takes the database into the restoring state. This guarantees that the database does not change after the tail-log backup. The log will be truncated unless the NO_TRUNCATE option or COPY_ONLY option is also specified.

 NOTE: Avoid using NO_TRUNCATE, except when the database is damaged.
CONTINUE_AFTER_ERROR Use CONTINUE_AFTER_ERROR only if you are backing up the tail of a damaged database.
When you use back up the tail of the log on a damaged database, some of the metadata ordinarily captured in log backups might be unavailable. 
Labels:

Comments

Post a Comment

Popular posts from this blog

Active Directory in Windows

                                                                Active Directory in Windows  When a windows server is bought it comes with many capabilities like AD groups for security, IIS(Internet Information Services), ability to built own VPN and many more of which Active directories are created with a domain and the computers which access the network will be connecting to the domain controllers. Practically, there will be a cluster of domain controllers where data will be replicated all over the cluster.
Post a Comment
Read more

Internet.org

                                                        Internet.org Internet.org is a non-profitable oraganisation founded by facebook and six other companies to bring affordable access to internet services to less developed countries by increasing efficiency, and facilitating the development of new business models around the provision of Internet access. It has been criticized for violating net neutrality and favouring facebook's own services. An Indian journalist criticized it as" being just a Facebook proxy targeting India's poor" as it provides restricted Internet access to Reliance Telecom's subscribers in India.However, in early May 2015, Facebook announced that this platform would be opened to websites that met its criteria.           ...
Post a Comment
Read more