What logs does a full database backup contain?

Question

When restoring a full database backup, restore can use the no_log parameter to not restore logs (although this parameter is officially disabled), and then rebuild the ldf by replacing the mdf with a new library. This can avoid the excessive time required to restore log files during restoration if the backup library log files are too large.

From my previous learning, I remembered that a full backup might contain some very few logs, which made me think that the no_log might lose some logs and be incomplete. But I was curious about exactly what aspect of the log was missing and why, which gave me a sense of what was missing when using the above scenario. I then looked through the official documents but could not find a specific description of what logs were included. Here is a partial excerpt from the official documents I found

https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/full-database-backups-sql-server?view=sql-server-ver16

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.

What exactly does this part of the transaction log refer to?

One guess I have is an unpersisted transaction log cache, that is, 60KB of transaction logs.

Answer 1

Hi @博雄 胡,

Thank you for reaching out and welcome to Microsoft Q&A!

What exactly does this part of the transaction log refer to?

It refers to a transaction log file that records the details of all modification operations in the database exactly. All log files need to be backed up to ensure data integrity and consistency.

In fact, the same is true for differential backups. A full or differential backup requires logs to restore the database to a transactionally consistent state when the full or differential backup ends. Therefore, a full or differential backup includes a portion of the transaction log backup. To be precise, a transaction log backup from the start to the end of a full backup.

To be more specific:

When the backup starts, SQL Server copies everything in the database and also includes portions of the transaction log that are needed while the backup is in progress.

To maintain consistency for either a full or a differential backup, SQL Server records the current log sequence number (LSN) at the time the backup starts and then again at the time the backup ends. This allows the backup to also capture the relevant parts of the log. The relevant part starts with the oldest open transaction at the time of the first recorded LSN and ends with the second recorded LSN.

In addition, please refer to this known thread, I get the information below:

Both full and diff backups contain 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.

Then, 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.

Hope my explanation can help you resolve your confusion.

Feel free to share your issues here if you have any concerns!

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications