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

Labels:

Comments

Post a Comment

Popular posts from this blog

SQL Server

                                                                     SQL Server  Its been a while that I have updated my blog. Though Databases and SQL was something that I used to stay away as much as possible because for some reasons, I got to work on these all the day for 8-9 hours and sometimes haunting in the nights and weekends as well. However, it has been a good journey so far and I found some interesting stuff in SQL Server. So, this triggered an idea a couple of weeks back to share some cool stuff that I am learning in SQL Server over numerous sources. Hope I can make this a good series. As there are not much visitors to my blog, le...
4 comments
Read more

An TLS 1.2 connection request was received from a remote client application, but none of the cipher suites supported by the client application are supported by the server. The TLS connection request has failed.

If the certificate being used on the server was generated using the Legacy Key option in the certificate request form, the private key for that certificate will be stored in Microsoft's legacy Cryptographic API framework. When the web server tries to process requests using its new, Cryptographic Next Generation (CNG) framework, it appears that something related to the RSA private key stored in the legacy framework is unavailable to the new framework. As a result, the use of the RSA cipher suites is severely limited. To avoid the issue, you can try to generate the certificate request using the CNG Key template in the custom certificate request wizard.
Post a Comment
Read more

One-third of people 'would upgrade' to a Facebook phone - is Zuckerberg's latest project a serious rival to iPhone?

One-third of phone users would definitely upgrade to a Facebook phone  - and 73% think the phone is a ‘good idea’. News of the phone emerged, with sources claiming that Facebook had hired ex-Apple engineers to work on an ‘official’ Facebook phone.     It’s not the first Facebook phone to hit the market – the social network giant has previously partnered with INQ and HTC to produce Facebook-oriented handsets, including phones with a  built-in ‘Like’ button. It could be a major hit – a flash poll of 968 people conducted by MyVoucherCodes found that 32% of phone users would upgrade as soon as it became available. The key to its success could be porting apps to mobile – something Facebook is already doing. Separate camera and chat apps already separate off some site functions, and third-party apps will shortly be available via  a Facebook app store. Of those polled, 57% hoped that it would be cheaper than an iPhone – presumably support...
Post a Comment
Read more