Does a SQL Server Log Backup Include Log Records Backed Up in a Previous Full or Differential Backup?

Question

I've been reading the Microsoft documentation on log backups in SQL Server and came across the following statement:

(Log Backup) Specifies a backup of the transaction log only. The log is backed up from the last successfully executed log backup to the current end of the log. Before you can create the first log backup, you must create a full backup."

From this, I understand that a log backup backs up the log records from the last log backup. However, I also know that differential and full backups include some log records as well. This leads me to my question:

Do log backups also include the log records that were backed up in the previous full or differential backups? Or does a log backup strictly back up log records starting from the last log backup, with full or differential backups not intervening or affecting what is backed up in the subsequent log backup?

Any insights would be greatly appreciated. Thanks in advance!

Answer 1

A log backup taken after a full or differential backup will include any log records created between the time of the last log backup and the time of the current log backup.

A full backup includes copies of all the database, as well as all log records from the LSN of the first copied database page to the LSN of the last copied database page.

During restore, data is copied into the new database, and then the log records are replayed to restore the database to the point in time when the backup was completed.

If you restore WITH RECOVERY, all transactions in the full backup will be rolled back and the database will be brought online.

If you restore WITH NORECOVERY, you can restore additional log backups to restore the database to a later point in time.

Answer 2

Both full and diff backups contains the log records produced during the time it took to copy the data pages to the backup media. I.e., when the backup starts, a checkpoint is performed. This is the data page copying phase. Log records can be produced during this phase (modifications are done in the database). After this data copying phase, those log records that were produced since last checkpoint will also be written to the backup media. Possibly a bit more than that if you are picky: Does a SQL Server Log Backup Include Log Records Backed Up in a Previous Full or Differential Backup?

The log backups will also contain above log records. That is why you can "skip" restoring a full or diff backup. All you need is an unbroken chain of log backups. Say you have these backups:

F1, L2, L3, F4, L5, L6

You can restore below:

F1, L2, L3, L5, L6

Above wouldn't be possible unless those log backups contains all log records. I.e., they form an unbroken chain of log backups (containing an unbroken chain of log records).

Answer 3

Your statement is not quite correct:

However, I also know that differential and full backups include some log records as well.

Full Backup

A full backup contains only a small portion of the transaction log so that SQL Server can create a database consisting of a *.MDF file and a *.LDF file.

A full database backup backs up the whole database. This includes part of the transaction log so that the full database can be recovered after a full database backup is restored. Full database backups represent the database at the time the backup finished.

^{Reference: Full Database Backups (SQL Server) (Microsoft Learn | SQL)}

You can view the contents of a backup file with the following command:

RESTORE FILELISTONLY FROM DISK = 'F:\BACKUP_LOCATION\StackExchange_FULL_20231021_223000.bak'

This will return a list of files that define the database.

+-------------------+---------------------------------------------+------+---------------+----------+----------------+--------+-----------+---------+--------------------------------------+-------------+--------------+-------------------+-----------------+-------------+--------------+---------------------+--------------------------------------+------------+-----------+---------------+-------------+
|    LogicalName    |                PhysicalName                 | Type | FileGroupName |   Size   |    MaxSize     | FileId | CreateLSN | DropLSN |               UniqueId               | ReadOnlyLSN | ReadWriteLSN | BackupSizeInBytes | SourceBlockSize | FileGroupId | LogGroupGUID | DifferentialBaseLSN |         DifferentialBaseGUID         | IsReadOnly | IsPresent | TDEThumbprint | SnapshotUrl |
+-------------------+---------------------------------------------+------+---------------+----------+----------------+--------+-----------+---------+--------------------------------------+-------------+--------------+-------------------+-----------------+-------------+--------------+---------------------+--------------------------------------+------------+-----------+---------------+-------------+
| StackExchange     | E:\SQL2022CS\SQL_DATA\StackExchange.mdf     | D    | PRIMARY       |  8388608 | 35184372080640 |      1 |         0 |       0 | F326A779-A4C1-4E02-8B2C-81D34D2DC71B |           0 |            0 |           4587520 |            4096 |           1 | NULL         |   42000000520800001 | E5AB4046-80EA-4F08-82FC-DFB2703583F5 |          0 |         1 | NULL          | NULL        |
| StackExchange_log | F:\SQL2022CS\SQL_LOGS\StackExchange_log.ldf | L    | NULL          | 75497472 |  2199023255552 |      2 |         0 |       0 | 3B04B400-C534-4822-AF24-D9980391FD0F |           0 |            0 |                 0 |            4096 |           0 | NULL         |                   0 | 00000000-0000-0000-0000-000000000000 |          0 |         1 | NULL          | NULL        |
+-------------------+---------------------------------------------+------+---------------+----------+----------------+--------+-----------+---------+--------------------------------------+-------------+--------------+-------------------+-----------------+-------------+--------------+---------------------+--------------------------------------+------------+-----------+---------------+-------------+

Differential Bakcup

The Differential Backup contains only the [Extents] that where modified in the database since the last full backup. The backup process scans the extents (8 pages = 1 extent; 1 page = 8 kb data) of the database file *.mdf and any other *.ndf files that make up the database, that contain a marker that the pages has been changed since the last backup.

A differential backup is based on the most recent, previous full data backup. A differential backup captures only the data that has changed since that full backup. The full backup upon which a differential backup is based is known as the base of the differential. Full backups, except for copy-only backups, can serve as the base for a series of differential backups, including database backups, partial backups, and file backups. The base backup for a file differential backup can be contained within a full backup, a file backup, or a partial backup.

and...

A differential backup captures the state of any extents (collections of eight physically contiguous pages) that have changed between when the differential base was created and when the differential backup is created.

^{Reference: Differential backups (SQL Server) (Microsoft Learn | SQL)}

Contrary to your understanding, the differential backup does not contain a part of the transaction log.

Here is picture of the contents of a differential backup:

Transaction Log Backup

A Transaction Log Backup is a backup of your transaction log file that contains the transaction and modifications made in the database since your last full backup (initial TLOG backup) or since the previous Transaction Log Backup. It is essentially a chain of modifications.

Every SQL Server database has a transaction log that records all transactions and the database modifications that are made by each transaction. The transaction log is a critical component of the database and, if there's a system failure, the transaction log might be required to bring your database back to a consistent state.

^{Reference: SQL Server transaction log architecture and management guide (Microsoft Learn | SQL)}

So a Transaction Log Backup is a copy of the transaction and modifications that where performed against your database.

Answering Your Questions

Do log backups also include the log records that were backed up in the previous full or differential backups?

Sort of, the Transaction Log Backups contain all modifications made after/during the last Full Backup. The transaction log backup in the Full Backup is only to facilitate the restore of the database (creates *.mdf and *.ldf file) and to bring the database into a consistent state. The Differential Backup does not affect the Transaction Log backups.

Or does a log backup strictly back up log records starting from the last log backup, with full or differential backups not intervening or affecting what is backed up in the subsequent log backup?

After you have an initial Full Backup, yes.

Let Me Explain

If you have the following backup chain:

F = Full D = Differential L = Transaction Log Backup

M--------------T--------------W--------------T------------F----X----- (Days of Week)
F -> L -> L -> D -> L -> L -> D -> L -> L -> F -> L -> -> D -> L      (Backups)

...then you can restore the database to Friday at the position marked with an X by using the Full Backup of Monday (F) and all Transaction Log Backups (L) up until X.

Or you could use the Full Backup (F) of Monday and the Differential Backup (D) of Wednesday and all the Transaction Log Backups (L) up until Friday to get to the position X.

I posted an example in my answer to the question How to tell if a backup log chain is broken?

Essentially it boils down to: If you have an unbroken chain of Transaction Log Backups up until a given point and a Full Backup as a base, then you can restore to any point along that chain, because the Transaction Log Backups contain all transactions and modifications since the last Transaction Log Backup or the initial Full Backup.