Iyfjky

Question

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?

I would highly recommend you read Understanding Logging and Recovery in SQL Server, particularly the sectioned entitled What Is Recovery?. — 2 hours ago
You already have an answer here: stackoverflow.com/questions/52588655/â€¦ — 1 hour ago

score 2 · Answer 1 · 2018-10-10 14:31:45Z

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?

Every restore operation leaves the database at some LSN. When restoring a log backup any log records with an LSN less than that of the database being restored are skipped.

hot2use 7,69251951 · Answer 2 · 2018-10-10 14:44:18Z

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:SQLBackupStackExchangeLOGStackExchange_LOG_20180915_161501.trn 0 0 286000001068100001 286000001068400001 286000001016900037 286000001063000002 NULL NULL NULL Microsoft SQL Server NT SERVICESQLSERVERAGENT EOR
StackExchange 2018-09-15 17:15:01.000 2018-09-15 17:15:01.000 Log C:SQLBackupStackExchangeLOGStackExchange_LOG_20180915_171501.trn 0 0 286000001068400001 286000001068700001 286000001016900037 286000001063000002 NULL NULL NULL Microsoft SQL Server NT SERVICESQLSERVERAGENT EOR
StackExchange 2018-09-15 18:15:01.000 2018-09-15 18:15:01.000 Log C:SQLBackupStackExchangeLOGStackExchange_LOG_20180915_181501.trn 0 0 286000001068700001 286000001069000001 286000001016900037 286000001063000002 NULL NULL NULL Microsoft SQL Server NT SERVICESQLSERVERAGENT EOR
StackExchange 2018-09-15 19:15:01.000 2018-09-15 19:15:01.000 Log C:SQLBackupStackExchangeLOGStackExchange_LOG_20180915_191501.trn 0 0 286000001069000001 286000001069300001 286000001016900037 286000001063000002 NULL NULL NULL Microsoft SQL Server NT SERVICESQLSERVERAGENT EOR
StackExchange 2018-09-15 20:15:01.000 2018-09-15 20:15:01.000 Log C:SQLBackupStackExchangeLOGStackExchange_LOG_20180915_201501.trn 0 0 286000001069300001 286000001069600001 286000001016900037 286000001063000002 NULL NULL NULL Microsoft SQL Server NT SERVICESQLSERVERAGENT EOR
StackExchange 2018-09-15 21:15:01.000 2018-09-15 21:15:01.000 Log C:SQLBackupStackExchangeLOGStackExchange_LOG_20180915_211501.trn 0 0 286000001069600001 286000001070800001 286000001016900037 286000001070100001 NULL NULL NULL Microsoft SQL Server NT SERVICESQLSERVERAGENT EOR
StackExchange 2018-09-15 22:15:01.000 2018-09-15 22:15:01.000 Log C:SQLBackupStackExchangeLOGStackExchange_LOG_20180915_221501.trn 0 0 286000001070800001 286000001071100001 286000001016900037 286000001070100001 NULL NULL NULL Microsoft SQL Server NT SERVICESQLSERVERAGENT EOR
StackExchange 2018-09-15 22:30:06.000 2018-09-15 22:30:06.000 Full C:SQLBackupStackExchangeFULLStackExchange_FULL_20180915_223006.bak 0 0 286000001071600037 286000001073300001 286000001016900037 286000001071600037 NULL NULL NULL Microsoft SQL Server NT SERVICESQLSERVERAGENT 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.

score 2 · Answer 3 · 2018-10-10 14:31:45Z

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?

Every restore operation leaves the database at some LSN. When restoring a log backup any log records with an LSN less than that of the database being restored are skipped.

hot2use 7,69251951 · Answer 4 · 2018-10-10 14:44:18Z

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:SQLBackupStackExchangeLOGStackExchange_LOG_20180915_161501.trn 0 0 286000001068100001 286000001068400001 286000001016900037 286000001063000002 NULL NULL NULL Microsoft SQL Server NT SERVICESQLSERVERAGENT EOR
StackExchange 2018-09-15 17:15:01.000 2018-09-15 17:15:01.000 Log C:SQLBackupStackExchangeLOGStackExchange_LOG_20180915_171501.trn 0 0 286000001068400001 286000001068700001 286000001016900037 286000001063000002 NULL NULL NULL Microsoft SQL Server NT SERVICESQLSERVERAGENT EOR
StackExchange 2018-09-15 18:15:01.000 2018-09-15 18:15:01.000 Log C:SQLBackupStackExchangeLOGStackExchange_LOG_20180915_181501.trn 0 0 286000001068700001 286000001069000001 286000001016900037 286000001063000002 NULL NULL NULL Microsoft SQL Server NT SERVICESQLSERVERAGENT EOR
StackExchange 2018-09-15 19:15:01.000 2018-09-15 19:15:01.000 Log C:SQLBackupStackExchangeLOGStackExchange_LOG_20180915_191501.trn 0 0 286000001069000001 286000001069300001 286000001016900037 286000001063000002 NULL NULL NULL Microsoft SQL Server NT SERVICESQLSERVERAGENT EOR
StackExchange 2018-09-15 20:15:01.000 2018-09-15 20:15:01.000 Log C:SQLBackupStackExchangeLOGStackExchange_LOG_20180915_201501.trn 0 0 286000001069300001 286000001069600001 286000001016900037 286000001063000002 NULL NULL NULL Microsoft SQL Server NT SERVICESQLSERVERAGENT EOR
StackExchange 2018-09-15 21:15:01.000 2018-09-15 21:15:01.000 Log C:SQLBackupStackExchangeLOGStackExchange_LOG_20180915_211501.trn 0 0 286000001069600001 286000001070800001 286000001016900037 286000001070100001 NULL NULL NULL Microsoft SQL Server NT SERVICESQLSERVERAGENT EOR
StackExchange 2018-09-15 22:15:01.000 2018-09-15 22:15:01.000 Log C:SQLBackupStackExchangeLOGStackExchange_LOG_20180915_221501.trn 0 0 286000001070800001 286000001071100001 286000001016900037 286000001070100001 NULL NULL NULL Microsoft SQL Server NT SERVICESQLSERVERAGENT EOR
StackExchange 2018-09-15 22:30:06.000 2018-09-15 22:30:06.000 Full C:SQLBackupStackExchangeFULLStackExchange_FULL_20180915_223006.bak 0 0 286000001071600037 286000001073300001 286000001016900037 286000001071600037 NULL NULL NULL Microsoft SQL Server NT SERVICESQLSERVERAGENT 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.

Category

Random preview

After a full backup, how does system know which transactions to restore from the transaction log backup?

Assuming the Following Scenario

Steps to Restore:

Questions

Additional Scenario

Assuming the Following Scenario

Steps to Restore:

Questions

Additional Scenario

Assuming the Following Scenario

Steps to Restore:

Questions

Additional Scenario

Assuming the Following Scenario

Steps to Restore:

Questions

Additional Scenario

2 Answers 2

Covering The Basics

Getting Into The System

Example Output of My StackExchange Database

Answering Your Questions

...For SMSS

...For Transact-SQL

Summary

Your Answer

Sign up or log in

Post as a guest

Post as a guest

2 Answers 2

2 Answers 2

Covering The Basics

Getting Into The System

Example Output of My StackExchange Database

Answering Your Questions

...For SMSS

...For Transact-SQL

Summary

Covering The Basics

Getting Into The System

Example Output of My StackExchange Database

Answering Your Questions

...For SMSS

...For Transact-SQL

Summary

Covering The Basics

Getting Into The System

Example Output of My StackExchange Database

Answering Your Questions

...For SMSS

...For Transact-SQL

Summary

Covering The Basics

Getting Into The System

Example Output of My StackExchange Database

Answering Your Questions

...For SMSS

...For Transact-SQL

Summary

Sign up or log in

Post as a guest

Post as a guest

Sign up or log in

Post as a guest

Sign up or log in

Post as a guest

Sign up or log in

Post as a guest

Comments

Post a Comment

Popular posts from this blog

Long meetings (6-7 hours a day): Being â€œbabysatâ€ by supervisor

Is the Concept of Multiple Fantasy Races Scientifically Flawed? [closed]

Confectionery

2 Answers
2

2 Answers
2

2 Answers
2

Long meetings (6-7 hours a day): Being â€œbabysatâ€ by supervisor