Sql-server – After a full backup, how does system know which transactions to restore from the transaction log backup

backuprestoresql servertransaction-log

Full backup does not truncate the transaction log file.

Assuming the Following Scenario

Full backup at 6AM
TLog backup at 10AM
Full backup at 1PM
TLog backup at 6PM
Crash immediately at the next second (so no tail log backup needed)

Steps to Restore:

Restore full backup from point 3 (contains data as at 1PM)
Then, restore the tlog backup from step 4 (contains log from 10AM to 6PM)

Questions

During recovery, how does the system know that only certain portion of the log file (post 1PM [exclude 10AM to 1PM]) needs to be replayed on the database?
Does it check the timestamps with in the transaction log backup to compare it with the full backup? Or does it check the LSN?

Additional Scenario

Similarly, in another scenario, let's say we do a Full backup at 10AM, then take a transaction log backup at 12PM. The transaction log will have all transactions up-till 12PM (and even prior to 10AM assuming db existed prior to 10AM and there was no previous transaction log backup).

Now, when we restore the full backup and then apply the transaction log backup, how does the system know that only transactions in the log post 10AM need to be replayed? Since all those prior to 10AM will be already there as part of the full backup restore. Is it checked via timestamps or LSN's?

Best Answer

Each and every restore operation relies (in some part) on the LSN that is stored together with the backup and internally in the msdb database.

Covering The Basics

From some Microsoft documentation:

_{(emphasis mine)}

LSNs are used internally during a RESTORE sequence to track the point in time to which data has been restored. When a backup is restored, the data is restored to the LSN corresponding to the point in time at which the backup was taken. Differential and log backups advance the restored database to a later time, which corresponds to a higher LSN.

Every record in the transaction log is uniquely identified by a log sequence number (LSN). LSNs are ordered such that if LSN2 is greater than LSN1, the change described by the log record referred to by LSN2 occurred after the change described by the log record LSN.

The LSN of a log record at which a significant event occurred can be useful for constructing correct restore sequences. Because LSNs are ordered, they can be compared for equality and inequality (that is, <, >, =, <=, >=). Such comparisons are useful when constructing restore sequences.

^{Reference: Recover to a Log Sequence Number (Microsoft | Docs | SQL Server)}

After reading through some loops you will eventually land on the Microsoft Documentation page for SQL Server Transaction Log Architecture and Management Guide

_{(emphasis mine)}

The SQL Server transaction log operates logically as if the transaction log is a string of log records. Each log record is identified by a log sequence number (LSN). Each new log record is written to the logical end of the log with an LSN that is higher than the LSN of the record before it. Log records are stored in a serial sequence as they are created. Each log record contains the ID of the transaction that it belongs to. For each transaction, all log records associated with the transaction are individually linked in a chain using backward pointers that speed the rollback of the transaction.

^{Reference: SQL Server Transaction Log Architecture and Management Guide (Microsoft | Docs | SQL Server)}

Getting Into The System

When you query the msdb database with a script I created for my own purpose, you will receive an overview of the relevant LSN numbers that are stored together with the FULL, DIFF and TLOG backups.

SELECT  
/* Columns for retrieving information */
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   -- msdb.dbo.backupset.expiration_date, 
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Full'  
       WHEN 'I' THEN 'Diff'
       WHEN 'L' THEN 'Log'  
   END AS backup_type,  
   -- msdb.dbo.backupset.backup_size / 1024 / 1024 as [backup_size MB],  
   -- msdb.dbo.backupmediafamily.device_type,
   msdb.dbo.backupmediafamily.physical_device_name,
   -- msdb.dbo.backupmediafamily.logical_device_name,
   -- msdb.dbo.backupset.name AS backupset_name, 
   -- msdb.dbo.backupset.description,
   msdb.dbo.backupset.is_copy_only,
   msdb.dbo.backupset.is_snapshot,   
   msdb.dbo.backupset.first_lsn,
   msdb.dbo.backupset.last_lsn,
   msdb.dbo.backupset.database_backup_lsn,
   msdb.dbo.backupset.checkpoint_lsn,
   msdb.dbo.backupset.differential_base_lsn,
   msdb.dbo.backupset.fork_point_lsn,
   msdb.dbo.backupmediaset.name,
   msdb.dbo.backupmediaset.software_name,
   msdb.dbo.backupset.user_name,
   'EOR'

FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset 
   ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
   INNER JOIN msdb.dbo.backupmediaset
   on msdb.dbo.backupmediaset.media_set_id = backupmediafamily.media_set_id


/* ----------------------------------------------------------------------------
        Generic WHERE statement to simplify selection of more WHEREs    
-------------------------------------------------------------------------------*/
WHERE 1 = 1
AND     database_name IN ('<name_of_your_database>')

This will give you an idea of the LSNs that are stored in the backup files. The msdb doesn't contain a full list of all LSNs in the backups, but just the main ones considered vital for a manual restore of a database:

msdb.dbo.backupset.first_lsn
msdb.dbo.backupset.last_lsn
msdb.dbo.backupset.database_backup_lsn
msdb.dbo.backupset.checkpoint_lsn
msdb.dbo.backupset.differential_base_lsn
msdb.dbo.backupset.fork_point_lsn

A description of these columns can be found in the official Microsoft Documentation for the backupset table.

Example Output of My StackExchange Database

database_name  backup_start_date  backup_finish_date                           backup_type physical_device_name                                                    is_copy_only   is_snapshot  first_lsn            last_lsn            database_backup_lsn  checkpoint_lsn     differential_base_lsn  fork_point_lsn  name  software_name       user_name                  (No column name)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
StackExchange  2018-09-15         16:15:01.000        2018-09-15 16:15:01.000  Log         C:\SQL\Backup\StackExchange\LOG\StackExchange_LOG_20180915_161501.trn   0              0             286000001068100001  286000001068400001  286000001016900037  286000001063000002  NULL                  NULL            NULL  Microsoft SQL Server  NT SERVICE\SQLSERVERAGENT  EOR
StackExchange  2018-09-15         17:15:01.000        2018-09-15 17:15:01.000  Log         C:\SQL\Backup\StackExchange\LOG\StackExchange_LOG_20180915_171501.trn   0              0             286000001068400001  286000001068700001  286000001016900037  286000001063000002  NULL                  NULL            NULL  Microsoft SQL Server  NT SERVICE\SQLSERVERAGENT  EOR
StackExchange  2018-09-15         18:15:01.000        2018-09-15 18:15:01.000  Log         C:\SQL\Backup\StackExchange\LOG\StackExchange_LOG_20180915_181501.trn   0              0             286000001068700001  286000001069000001  286000001016900037  286000001063000002  NULL                  NULL            NULL  Microsoft SQL Server  NT SERVICE\SQLSERVERAGENT  EOR
StackExchange  2018-09-15         19:15:01.000        2018-09-15 19:15:01.000  Log         C:\SQL\Backup\StackExchange\LOG\StackExchange_LOG_20180915_191501.trn   0              0             286000001069000001  286000001069300001  286000001016900037  286000001063000002  NULL                  NULL            NULL  Microsoft SQL Server  NT SERVICE\SQLSERVERAGENT  EOR
StackExchange  2018-09-15         20:15:01.000        2018-09-15 20:15:01.000  Log         C:\SQL\Backup\StackExchange\LOG\StackExchange_LOG_20180915_201501.trn   0              0             286000001069300001  286000001069600001  286000001016900037  286000001063000002  NULL                  NULL            NULL  Microsoft SQL Server  NT SERVICE\SQLSERVERAGENT  EOR
StackExchange  2018-09-15         21:15:01.000        2018-09-15 21:15:01.000  Log         C:\SQL\Backup\StackExchange\LOG\StackExchange_LOG_20180915_211501.trn   0              0             286000001069600001  286000001070800001  286000001016900037  286000001070100001  NULL                  NULL            NULL  Microsoft SQL Server  NT SERVICE\SQLSERVERAGENT  EOR
StackExchange  2018-09-15         22:15:01.000        2018-09-15 22:15:01.000  Log         C:\SQL\Backup\StackExchange\LOG\StackExchange_LOG_20180915_221501.trn   0              0             286000001070800001  286000001071100001  286000001016900037  286000001070100001  NULL                  NULL            NULL  Microsoft SQL Server  NT SERVICE\SQLSERVERAGENT  EOR
StackExchange  2018-09-15         22:30:06.000        2018-09-15 22:30:06.000  Full        C:\SQL\Backup\StackExchange\FULL\StackExchange_FULL_20180915_223006.bak 0              0             286000001071600037  286000001073300001  286000001016900037  286000001071600037  NULL                  NULL            NULL  Microsoft SQL Server  NT SERVICE\SQLSERVERAGENT  EOR

The exact LSNs are stored in the backup files themselves and can be retrieved using 3-rd party tools.

Answering Your Questions

...For SMSS

When you start a restore process a couple of statements are execute in the background, which result in the GUI presenting you with an overview of FULL, DIFF and TLOG backups that would be required to bring your database back into a consistent state. The actual RESTORE DATABASE ... and RESTORE LOG ... will then determine which portions of the corresponding backup files are required to bring the database back into a consistent state, according to the documentation previously mentioned.

...For Transact-SQL

When you manually have to restore a database, then you will have to look into the msdb database (possibly with the aid of my script) to determind which files you require to bring your database back into a consistent state. The actual RESTORE DATABASE ... and RESTORE LOG ... will then determine which portions of the corresponding backup files are required to bring the database back into a consistent state, according to the documentation previously mentioned.

Summary

The magic is in the LSN stored in the backup files and restore process coded by Microsoft.

You can obtain a license to allow you to plug in to various APIs that would allow you to read more information from the backup files. For that you would have to contact a Microsoft representative.