Tail Log Backups

In my SQL Server Recovery Models blog, I touched a bit on my experience with recovery using Tail Log Backup.  In this post we will take an in-depth look at Tail Log Backup; what they are, why they should be in your toolbelt, and lastly line out the steps to successfully take a Tail Log Backup.

What is a Tail Log Backup?

Simply put, a Tail Log Backup contains log records that were not yet backed up at the time of failure.  So if Transaction Log Backups occur every 15 minutes, and you suffered loss at the 11 minute mark, the Tail Log Backup includes all data changes during the time span between the last successful Transaction Log Backup and minute 11.    Recovery using the Tail Log backups can be performed in either Full Recovery or Bulk Logged Recovery, but cannot be used in Simple Recovery Model. 

Why are these important?

Is it possible to recover with no data loss?  YES*!  This is where our new friend comes into action!   Taking a Tail Log backup is done to prevent data loss and helps recover right up to the point of disaster. (This is also referred to as Point In Time Restore.)

Keep in mind: in order to recover your database to its most recent point, you must have a valid Full Backup and valid Transaction Log Backup sequence!

After a disaster, if you can take a Tail Log Backup, have all the preceding Transaction Log Backups, have a valid Full Backup, and you are in Full Recovery mode, it is possible to recover with NO DATA LOSS!  For this to be possible in Bulk-Logged Recovery mode, no minimally logged operations must have occurred.

In what case would you ever need a Tail Log Backup?

Any time you have a damaged database and are needing to restore, it is best to check to see if you need a Tail Log Backup.  The question you need to ask is “Do I have Transaction Log Backups?”   If the answer is yes, your recovery will be much faster!  Another question to ask is “Is the Server still available?”

Server Still Available

If the database is damaged but the server is still available, it is pretty easy to take a Tail Log Backup.  When the data files are damaged or missing, you will get an error if you try to take a normal log backup.  But if you use NO TRUNCATE, you will be able to take a Log Backup.

 
BACKUP LOG [TestDB] TO DISK = 'G:\DBA\Backups\TestDB_Log_Tail.bck' ;
WITH INIT,
NO_TRUNCATE;

*Note:  In order to successfully take a Tail Log Backup, you must use NO TRUNCATE. That will allow the log backup even if the database files are damaged or not there.  Using INIT will overwrite any existing set and you will still end up with only one backup in case the command is run twice.

Server Not Available

Let’s say the server has crashed and cannot be brought back online.  If you are lucky enough to still have access to all the Full Backups and Log Backup files, you can attach them to another server and automatically recover.

If the database is damaged and the server is not available, taking a Tail Log Backup becomes a little more difficult. Rest assured, there is still an option to try.

You will need to create a dummy database with the same name as the one that is damaged.

1. Next, set the database offline.
2. Delete the files from the dummy database.
3. Drop in the log file from the real database.

--Create a dummy database with the same name
CREATE DATABASE [TestDB];
GO
--Set the database offline
ALTER DATABASE [TestDB] SET OFFLINE;
GO
--Insert Log file from original database--Take Tail Log Backup
BACKUP LOG [TestDB] TO DISK = 'G:\DBA\Backups\TestDB_Log_Tail.bck' ;
WITH INIT,
NO_TRUNCATE;
GO

Now you are ready to take a Tail Log Backup as detailed above.  This will allow you to recover to the point of failure!  In my next post, we will do a deep dive into Recovery Using Tail Log Backups.

Thank you for reading!