Why does a data backup include transaction log?

Question: I’m what you call an ‘involuntary’ DBA and I’ve noticed something strange. Whenever I do a database backup, SQL Server says that it backed up the data files and the transaction log. Why does it need to back up the transaction log too?

Answer: The simple answer is that a data backup must include some transaction log otherwise the backup isn’t valid. When a database backup is restored, the result must be a transactionally consistent database (i.e. with no uncommitted transactions or structural inconsistencies).

SQL Server does not pause insert/update/delete activity in the database while it is being backed up, which means that the database can be changing while it is being backed up. Imagine the case of a database that includes a simple table with a single non-clustered index, with the nonclustered index page near the start of the data file and the table data page near the end of the file.

The backup starts reading from the data file. It reads the nonclustered index page. Then an insert occurs that inserts a data record in the table data page and an index record in the nonclustered index page. Another checkpoint occurs which flushes these two pages to disk. All the while the backup is reading from the data file. It then reads the table data page.

You can see how it is perfectly possible for the pre-insert version of the nonclustered index page to be in the backup, and the post-insert version of the table data page to be in the backup. If the backup does not include any transaction log, the restored database from our backup will have an inconsistency; a table data record that does not have a matching nonclustered index record.

However, a backup DOES include some transaction log. So in my example, the log records for the inserts into the two pages would be included in the backup and then analyzed during the restore. The log record that does the insert into the table data page will be ignored because the backup already contains the post-insert page image. The log record that does the insert into the nonclustered index page will be replayed – performing the insert on the page – as SQL Server can tell that the index page image is old and needs to be brought up-to-date.

The amount of transaction log that a data backup includes is from the end of the data-reading portion of the backup all the way back to the oldest point determined by:

There’s a lot more going on under the covers but this should give you a good understanding of why a data backup needs to include transaction log: to allow the database to restore to a transactionally consistent point in time (as of the end of the data reading portion of the backup operation).

Books Online has a lot more information on this, as well as three feature articles I wrote for TechNet Magazine in 2009 on logging and recovery, backup internals, and restore internals.