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

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;







up vote
1
down vote

favorite












Full backup does not truncate the transaction log file.



Assuming the Following Scenario



  1. Full backup at 6AM

  2. TLog backup at 10AM

  3. Full backup at 1PM

  4. TLog backup at 6PM

  5. 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?










share|improve this question









New contributor




variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.















  • 2




    I would highly recommend you read Understanding Logging and Recovery in SQL Server, particularly the sectioned entitled What Is Recovery?.
    – Scott Hodgin
    2 hours ago










  • You already have an answer here: stackoverflow.com/questions/52588655/…
    – Denis Rubashkin
    1 hour ago
















up vote
1
down vote

favorite












Full backup does not truncate the transaction log file.



Assuming the Following Scenario



  1. Full backup at 6AM

  2. TLog backup at 10AM

  3. Full backup at 1PM

  4. TLog backup at 6PM

  5. 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?










share|improve this question









New contributor




variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.















  • 2




    I would highly recommend you read Understanding Logging and Recovery in SQL Server, particularly the sectioned entitled What Is Recovery?.
    – Scott Hodgin
    2 hours ago










  • You already have an answer here: stackoverflow.com/questions/52588655/…
    – Denis Rubashkin
    1 hour ago












up vote
1
down vote

favorite









up vote
1
down vote

favorite











Full backup does not truncate the transaction log file.



Assuming the Following Scenario



  1. Full backup at 6AM

  2. TLog backup at 10AM

  3. Full backup at 1PM

  4. TLog backup at 6PM

  5. 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?










share|improve this question









New contributor




variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











Full backup does not truncate the transaction log file.



Assuming the Following Scenario



  1. Full backup at 6AM

  2. TLog backup at 10AM

  3. Full backup at 1PM

  4. TLog backup at 6PM

  5. 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?







sql-server backup restore transaction-log






share|improve this question









New contributor




variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











share|improve this question









New contributor




variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









share|improve this question




share|improve this question








edited 2 hours ago









hot2use

7,69251951




7,69251951






New contributor




variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









asked 2 hours ago









variable

1244




1244




New contributor




variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





New contributor





variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.






variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.







  • 2




    I would highly recommend you read Understanding Logging and Recovery in SQL Server, particularly the sectioned entitled What Is Recovery?.
    – Scott Hodgin
    2 hours ago










  • You already have an answer here: stackoverflow.com/questions/52588655/…
    – Denis Rubashkin
    1 hour ago












  • 2




    I would highly recommend you read Understanding Logging and Recovery in SQL Server, particularly the sectioned entitled What Is Recovery?.
    – Scott Hodgin
    2 hours ago










  • You already have an answer here: stackoverflow.com/questions/52588655/…
    – Denis Rubashkin
    1 hour ago







2




2




I would highly recommend you read Understanding Logging and Recovery in SQL Server, particularly the sectioned entitled What Is Recovery?.
– Scott Hodgin
2 hours ago




I would highly recommend you read Understanding Logging and Recovery in SQL Server, particularly the sectioned entitled What Is Recovery?.
– Scott Hodgin
2 hours ago












You already have an answer here: stackoverflow.com/questions/52588655/…
– Denis Rubashkin
1 hour ago




You already have an answer here: stackoverflow.com/questions/52588655/…
– Denis Rubashkin
1 hour ago










2 Answers
2






active

oldest

votes

















up vote
2
down vote














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.






share|improve this answer





























    up vote
    2
    down vote













    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.






    share|improve this answer




















      Your Answer







      StackExchange.ready(function()
      var channelOptions =
      tags: "".split(" "),
      id: "182"
      ;
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function()
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled)
      StackExchange.using("snippets", function()
      createEditor();
      );

      else
      createEditor();

      );

      function createEditor()
      StackExchange.prepareEditor(
      heartbeatType: 'answer',
      convertImagesToLinks: false,
      noModals: false,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: null,
      bindNavPrevention: true,
      postfix: "",
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      );



      );






      variable is a new contributor. Be nice, and check out our Code of Conduct.









       

      draft saved


      draft discarded


















      StackExchange.ready(
      function ()
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f219758%2fafter-a-full-backup-how-does-system-know-which-transactions-to-restore-from-the%23new-answer', 'question_page');

      );

      Post as a guest






























      2 Answers
      2






      active

      oldest

      votes








      2 Answers
      2






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes








      up vote
      2
      down vote














      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.






      share|improve this answer


























        up vote
        2
        down vote














        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.






        share|improve this answer
























          up vote
          2
          down vote










          up vote
          2
          down vote










          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.






          share|improve this answer















          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.







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited 1 hour ago

























          answered 2 hours ago









          David Browne - Microsoft

          9,128725




          9,128725






















              up vote
              2
              down vote













              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.






              share|improve this answer
























                up vote
                2
                down vote













                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.






                share|improve this answer






















                  up vote
                  2
                  down vote










                  up vote
                  2
                  down vote









                  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.






                  share|improve this 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: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.







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered 1 hour ago









                  hot2use

                  7,69251951




                  7,69251951




















                      variable is a new contributor. Be nice, and check out our Code of Conduct.









                       

                      draft saved


                      draft discarded


















                      variable is a new contributor. Be nice, and check out our Code of Conduct.












                      variable is a new contributor. Be nice, and check out our Code of Conduct.











                      variable is a new contributor. Be nice, and check out our Code of Conduct.













                       


                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function ()
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f219758%2fafter-a-full-backup-how-does-system-know-which-transactions-to-restore-from-the%23new-answer', 'question_page');

                      );

                      Post as a guest













































































                      Comments

                      Popular posts from this blog

                      What does second last employer means? [closed]

                      Installing NextGIS Connect into QGIS 3?

                      One-line joke