In this blog post I will hopefully take you through what a “Tail of the Log” or “Tail-Log” back up is, how you can create one and why this is important in our SQL Server environment. So let’s start out by working out what a Tail of the Log is.
In our SQL Server environment we will have multiple databases split basically across 2 types, System Databases and User Databases. System Databases are created when an instance of SQL Server is created. These databases are a requirement for the instance of SQL Server to be up and running and should have an appropriate backup strategy applied to allow for recovering from an issue.
User Databases are created either by vendor applications or internally to meet specific requirements of the business using them. Depending on the type of database and the criticality of the data stored and the ability to reproduce or recover the data in the event of a disaster will be a factor in dictating the backup and recovery strategy used. A Database can be in 1 of 3 recovery models: Full, Simple and Bulk Logged. More information on the types of recovery models is available on MSDN. For the purpose of this article we are only interested in databases in either the full or bulk logged recovery models.
As a Database holds our business data it probably is pretty important to us. If you were working for yourself and running your own business and your database server died or your database died, could you recover? When data is inserted in the database, updated or deleted this all happens within a transaction. A transaction is either committed or rolled back depending on the success of the transaction. This is a requirement to be able to keep the data in the database consistent. This is achieved by each transaction being captured with the appropriate information capturing what type of action is being undertaken and is captured in the transaction log.
With your database being in either the full or bulk logged recovery model to be able to recover the database back to a point in time will require as a minimum a full backup and a transaction log back up. Having said this, there are many factors that come into the design of the backup strategy being implemented. For this article we are keeping it very simple and are only going to talk about full backups and transaction log backups. When a full backup is taken it backs up the whole database plus part of the transaction log to allow for the database to be recovered. To read more on Full backups have a look at Database Backups on MSDN. With our database being in the full recovery model and lots of inserts, updates and deletes happening our transaction log is capturing all of these transactions. For us to be able to recover to a point in time in the event of a disaster these transactions are required to be backed up.
For this article let’s have a look at a time frame:
- Full backup at 10 pm
- Transaction log backup at 2 am
- Transaction log backup at 6 am
- Transaction log backup at 10 am
Transactions have been occurring constantly on the database since 10 pm. It is now 11 am and we can see that we have a full backup and 3 transaction log backups. At this point in time we experience an issue with our database and need to recover the database from our backups. As they stand right now we can only recover to 10 am as we are only part way through our 4 hourly transaction log backup schedule. Is this ok to just restore back to 10 am? That would mean losing 1 hour of data transactions. Is this acceptable? For some environments this may be acceptable and for a lot of others this is not. This portion of the transaction log containing the transactions that have been undertaken since the transaction log backup at 10 am is known as the “Tail of the Log”
In this scenario outlined or in any scenario that you are faced with where there is an issue with the database and you need to recover from backup, the first thing you need to do is take a “Tail of the Log backup”. This captures all of the transactions that are residing in the Tail of the Log since the last transaction log backup was undertaken and gives us the ability to recover the database up to the point in time of the last transaction.
To create a Tail of the Log backup we can use T-SQL, PowerShell or the GUI wizards available to us from within SQL Server Management Studio. These will all work if the instance of SQL Server is available. However if the server itself has crashed and if you have access to the data and log files there are steps available to try and gain access to them to be able to create the Tail of the Log backup. Paul Randal explains the process of creating the Tail of the Log backup very well in his post Disaster recovery 101: backing up the tail of the log
If you are new to SQL Server and learning about the Transaction log and the types of recovery models, always remember that in the event of a disaster on the database, the first thing you should do is try to take the Tail of the Log backup to give yourself as much chance as possible of recovering as much data as possible.
Leave a Reply