A transaction log backup contains all transaction log records that have been made between the last transaction log backup or the first full backup and the last log record that is created upon completion of the backup process. The transaction log backup allows to restore a database to a particular point-in-time before the failure has occurred. It is incremental, meaning that in order to restore a database to a certain point-in-time, all transaction log records are required to replay database changes up to that particular point-in-time. Please note that transaction log backup is available only for full or bulk-logged recovery models. The picture below shows how transaction log backup works.
Due to the fact that in full or bulk-logged recovery models transaction log is truncated when the transaction log backup is made, it is necessary to backup the log regularly in order to manage the size of the transaction log. Otherwise, the transaction log file will grow until there’s no more space left.
If there is no need in the point-in-time recovery and database is growing slowly it is better to use a simple recovery model (without transaction logs). In this case the transaction log is automatically truncated to remove any inactive virtual log files (every log record is stored in virtual log files and can have any number of them. The size of each virtual log file is not fixed). As far as the simple recovery model is concerned, the transaction log truncation takes place after each checkpoint.
Transaction Log Backup Chain
A transaction log backup chain is a continuous sequence of transaction log backups starting with first full database backup. Each backup from the chain has its own FirstLSN (oldest log record in the backup set) and LastLSN (the number of the next log record after the backup set), that help to restore transaction log backup files in the right sequence.
To get backup’s FirstLSN and LastLSN values you can use the following command:
RESTORE HEADERONLY FROM DISK = 'log.bak'
Though it’s a great opportunity to restore a database to the point-in-time with the help of a log backup chain there are some instances when the log backup chain is broken. Here are the two of such:
- Adding T-SQL options TRUNCATE_ONLY or NO_LOG after a BACKUP LOG command.
- Switching from full or bulk-logged recovery models to simple and back again.
On the other hand, here are the other two common instances in which the log backup chain is not broken:
- Starting a full, differential or file/filegroup backup
- Switching from full recovery model to bulk-logged and back again
Tail-log Backup
A tail-log backup captures all committed transactions that have been written from the moment of the last transaction log backup. Truth be told, every last transaction log backup is actually a tail-log backup. So what is the difference? Here is the case: Assume there was a failure and some data files were corrupted. Despite the fact that the data files were damaged or even destroyed, the whole transaction log can be backed up and restored on the standby database (note that it works only for full recovery model).
How to Make a Transaction Log Backup
Use the following T-SQL command to make transaction log backup:
BACKUP LOG your_database TO DISK = 'log.bak'
In order to create transaction log backup with SQL Server Management Studio (SSMS) right-click on the database you want to backup, select “Tasks”, then “Back up…”. Choose “Transaction Log” type, add backup destination and click “OK”.
The easiest way of creating scheduled transaction log backups is by using SQLBackupAndFTP utility:
