![\"Transaction](\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/Transaction-log-3-648x360.png\")
<\/p>\nA 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\u00a0to replay\u00a0database changes up to that particular point-in-time. Please note that transaction log backup is available\u00a0only for\u00a0full or bulk-logged recovery models. The picture below shows how transaction\u00a0log backup works.<\/p>\n
<\/p>\n
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.<\/p>\n
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.<\/p>\n
A transaction log backup chain is a continuous sequence of transaction log backups starting\u00a0with 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.<\/p>\n
![\"Transaction](\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/Transaction-log-backup-files.png\")
<\/p>\n
To get backup’s FirstLSN and LastLSN values you can use the following command:<\/p>\n
RESTORE HEADERONLY FROM DISK<\/span> = 'log.bak'<\/span><\/pre>\n![\"LSN\"](\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/LSN.png\")
<\/span><\/p>\nThough 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:<\/p>\n
\n- Adding T-SQL options TRUNCATE_ONLY or NO_LOG after a BACKUP LOG command.<\/li>\n
- Switching from full or bulk-logged recovery models to simple and back again.<\/li>\n<\/ul>\n
On the other hand, here are the other\u00a0two common instances in which\u00a0the log backup chain is not broken:<\/p>\n
\n- Starting a full, differential or file\/filegroup\u00a0backup<\/li>\n
- Switching from full recovery model to bulk-logged and back again<\/li>\n<\/ul>\n
Tail-log Backup<\/h2>\n
A\u00a0tail-log backup\u00a0captures all committed transactions that have been\u00a0written from the moment of\u00a0the 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\u00a0full recovery model).<\/p>\n
How to Make a Transaction Log Backup<\/h2>\n
![\"\"](\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/Advanced-Backup-Schedule-648x496.png\")
<\/p>\n","protected":false},"excerpt":{"rendered":"