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.
