A prevalent question since many years often resurfaces in my mailbox. I am writing this blog here so in the future, I can direct users to this blog posts when they ask about Tail-Log Backups.
Tail-Log Backups were introduced in SQL Server 2005, and I find it handy feature for the backup process.
Before we start any explanation, let me say this first that, this feature will only work if you have taken once full backup of your database and your database is in either full or bulk-logged recovery models. If you have not taken the full backup of your database, this feature will not work for you. Now let us continue understanding what Tail-Log Backups is actually.
Tail-Log Backups
A tail-log backup captures any log records which has not yet been backed up by the last transactional log backup.
There are multiple scenarios when we need to take this tail-log backup, but we will today focus on the one scenario which is restoring the online database.
When your database is online after taking the backup, there may be more transactional operations happen. This means when we try to restore the database, it may be possible that we may lose some transactions and often you also see that error that your database should take the tail-log backup before restoring the database. This has often confused quite a many DBA so far.
Well, to avoid losing the last few transactions, it is always a good idea to take tail-log backup with a norecovery option.
Here is the script to take the tail log backups.
BACKUP LOG SQLAuthority TO DISK = 'D:\Data\SQLAuthority_NORECOVERY.TRN' WITH NORECOVERY, STATS = 10
Once you take your tail log backup, your database will move to restoring state, and no further transactions will be possible on it. Please note that when you restore your database, your sequence of restoring your databases should be as following:
- Restore your full backup
- Restore any differential backups if available (if not, move to next step)
- Restore all the transactional log in the order since last full backup
- Restore the Tail-Log Backup with recovery option
If you face any issue while restoring your database, do reach out to me and I will be happy to help you.
Reference: Pinal Dave (https://blog.sqlauthority.com)
10 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?