Transaction log recovery in SQL Server

September 18th, 2010 Add Your Comments

How to use the transaction log of a database in order to perform a recovery operation, when is that possible, when should it be used and what are the advantages? In this post I will try to provide an answer to the above questions.

Important note

Restoring databases using transaction log backups is available only for databases that use the full recovery model. For the purpose of this document I will only consider these kind of databases unless explicitly stating otherwise.

There are multiple ways of checking the recovery model of database. One of them is by going to the SQL Server Management Studio, right click on the database, go to properties, then options and the recovery model would be displayed in a drop down in the upper part of the window.

Recovery process

In order to restore a database using transaction log backups you will also need a full backup of the database. Usually a full database backup file will have the .bak extension, while a transaction log backup will have the .trn extension. The restore operation is made of the following steps:

  • Do the full database backup restore. You can do this step by using the sql server command RESTORE DATABASE or by using the SQL Server Management Studio functionality for restoring databases. The one important aspect for this step is that the database should be recovered with the NORECOVERY option.
  • Restore the transaction log backups. The transaction log backups that will be restored need to be taken after the full database backup that was restored at the previous step. If there are multiple transaction log backups since the full database backup we say that we have a backup chain. The restore of a log backup chain must include the restore of each of its items in the order they were created. Use the RESTORE LOG command to restore a transaction log backup.

Bellow is an example of performing a database restore using a full database backup and a chain of transaction logs:

DECLARE @dbName varchar(50)
SET @dbName = 'Test'

RESTORE DATABASE @dbName
FROM DISK = 'C:\Temp\test.bak'
WITH NORECOVERY

RESTORE LOG @dbName
FROM DISK = 'C:\Temp\testLog1.trn'
WITH NORECOVERY

RESTORE LOG @dbName
FROM DISK = 'C:\Temp\testLog2.trn'
WITH NORECOVERY

................................................

RESTORE LOG @dbName
FROM DISK = 'C:\Temp\testLogn.trn'
WITH RECOVERY, STOPAT = '2010-09-18 12:00:00 PM'

In the above example we had a full database backup in the test.bak file and n transaction log backups. It is inportant to note that all restore operations, excepting the last one, are made using the NORECOVERY option. The last restore operation in the chain must use the RECOVERY option.

One very important aspect of restoring transaction logs is that it can be done until a certain point in time. This is accomplished with the STOPAT option which should be applied to the last log backup in the chain (check the above example). By using this capability, if a database is at some point corrupted by a certain operation, you can restore it until that point.

Transaction log backup strategies

Usually, on production databases, scheduled backups are performed. Since a full database backup can be a time and resources consuming operation it is not feasable to perform such backups very often. In order to achieve both good performance and reliable backups, a mixed strategy containing both full and transaction log backups can be used. For example a full backup might be taken once a day and a transaction log backup once an hour.

LDF vs TRN files

SqlServer manages its transactions by using the transaction log file, which usually has the .ldf extension. When performing transaction log backups they will result in files with the .trn extension.

There is a big difference between the two files described above. Transaction log files are used at any time a database is online to ensure the correct database transactional behaviour while transaction log backup files are a snapshot of the commited transactions from a given period and they can be used in restore operations.

In a full restore model, the transaction log file will keep holding all the commited transactions since the last log truncate operation. However those transactions cannot be read or used directly in a restore operation.

If you need to restore a database from a .ldf file you will need to convert it to a .trn file. There is no way of doing that outside a database. You can create .trn files only as a result of a backup operation on an existing database. So what you will need to do is to attach the .ldf file to a database with the structure of the original database and perform a transaction log backup which will result in a .trn file will that can be used in a restore operation.

There some 3rd party tools built on undocummented features of SQL Server that claim to be able to read .ldf files and show the transactions inside them as SQL statements.

Transaction log truncation

Since transactions are always performed on a database, the transaction log can grow indefinitely. In order to prevent that, truncate operations are performed on the transaction log. A truncate will delete all the committed transactions from the log.

By default, a truncate will take place on any transaction log backup operation (please note that this articale considers databases using the full recovery model). Because of this, two consecutive transaction log backups will not contain the same operations. If you have performed multiple transaction logs with truncate since the last full backup and you lost some of the transaction log backups, you will not be able to perform the restore.

Log sequence number and backup start date

Each transaction from the transaction log has an unique number called the Log sequence number (or shorthanded LSN). The LSN number reflects the order transactions have been performed. The later the transaction the greater the LSN number will be.

When performing a restore operation using a log chain, the elements in the chain have to be subsequent. Each transaction log backup has a FirstLSN and LastLSN which can be found out using the following command:

RESTORE HEADERONLY FROM DISK = 'C:\Temp\testLog1.trn'

If you are trying to restore testLog3.trn after testLog1.trn, the restore will fail because the LastLSN in testLog1.trn is less than the FirstLSN in testLog3.trn which means that they are not subsequent.

The RESTORE HEADERONLY operation should be used when you are not sure on the order backups have been taken and if the backups you have are subsequent or if there are missing backups in your chain. The command also displays the time a certain backup has been taken.

Comments are closed.