Tail-Log Backups capture the end of an available transaction log before a restore. I assess them within the intended recovery chain.
A tail-log backup can preserve transactions committed after the last log backup. The example concerns an online database in full or bulk-logged recovery. A usable data backup and log chain are required. Simple recovery does not support transaction log backups.
-- Planned restore template only: this takes the source database out of service.
-- BACKUP LOG [SQLAuthority]
-- TO DISK = N'D:\SQLBackups\SQLAuthority_tail.trn'
-- WITH NORECOVERY, STATS = 10;WITH NORECOVERY leaves the source database restoring, preventing further transactions. That changes availability. Coordinate it with the restore plan and required exclusive access.
- Restore the selected full backup with NORECOVERY.
- Restore an applicable differential backup with NORECOVERY, if one is part of the plan.
- Restore the required subsequent log backups in LSN order, including the tail-log backup.
- Recover the database at the intended point, using RECOVERY on the final restore when appropriate.
A differential backup can change the required starting point for log restores. Damaged databases have additional restrictions, and bulk-logged changes can limit recoverability. The log may be unavailable. An intentionally earlier recovery point may not require a tail-log backup.
Reference: Tail-log scenarios and restore restrictions.
A tail-log backup is not always possible or necessary, it is a recovery step determined by the restore scenario.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
11 Comments. Leave new
Tail log back was introduced in sql server 2000 we can take it with norecovery or with standby it depends.
I have T-log backup scheduled for every 30mins and client deleted the data by 4:04PM can I get the data back by taking tail backup…???
yes
so Tail log backup essentially has to have “NO_TRUNCATE” . I dont see that in the command at all ? Is my understanding wrong ?
What is the mean of “WITH Continue_After_Error”
This one feature confuses me often as I try to think of a scenario where I have to restore over an existing database. The only time I would have to restore over an existing database is if it got corrupted or unoperable. How do you carry out a log backup if it’s corrupted or not operable? If not, wouldn’t you just kick off your log backup job instead with it’s proper naming convention?
Can I take the tail log back up immediately after the full backup to restore it on another server rather than taking differential and transaction log backup, also if I am not taking .diff and .trn than directly taking tail backup after full backup will that be a good option for not missing any transactions ?
According to my knowledge, you need to take a tran after full and restore it. That’s what I do usually
This article help full for me.
Thanks
In a scenario where we’re migrating a db to a new server, if I have the app shut down and do a CHECKPOINT of the db followed by a full backup, do I still need to do a tail-log backup?
NO_TRUNCATE is compulsory right in TAIL log backup?