Full backup Scenario

  • Peter Trast

    SSCarpal Tunnel

    Points: 4332

    Paul White NZ (3/31/2010)


    Thank you, Peter. Of course, there are some edge cases and subtleties, but unless Mr Randal appears and tells me I know nothing about nothing...I'm happy with it.

    I don't sweat the exceptions. Concepts first, exceptions later. Can't understand the exceptions until the normal process makes sense. I get grief for using broken analogies to explain concepts like joins but the students like simple explanations, even if they are flawed by real world applications.

    Peter Trast
    Microsoft Certified ...(insert many literal strings here)
    Microsoft Design Architect with Alexander Open Systems

  • TomThomson

    SSC Guru

    Points: 104773

    Paul White NZ (3/31/2010)


    The whole backup operation is conceptually quite simple:

      Force a database checkpoint (flush all updated-in-memory pages to disk before anything is read by the backup)
      Record the minimum recovery LSN (LSN1)
      Database data read begins
      Database data read ends
      Record the current LSN (LSN2)
      Read the transaction log from LSN1 to LSN2

    Thanks Paul, that's a very clear description. It answers all the questions I had about whether dirty data is flushed first, when the start LSN is recorded, and whether the final LSN is allowed to be later than the current LSN at the end of the DB read.

    And I see the MS design does include flushing already dirty pages to disc despite Hugo's suggestion that this is not a useful thing to do.

    Tom

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Tom.Thomson (3/31/2010)


    And I see the MS design does include flushing already dirty pages to disc despite Hugo's suggestion that this is not a useful thing to do.

    Yes, I noticed that as well. And was a bit surprised by it.

    I'm glad Paul was able to find and post such a detailed description of the process. Thanks, Paul!


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Paul White

    SSC Guru

    Points: 150442

    Thanks, everyone. Glad I am not the only one to find this whole area fascinating.

  • Festeron

    SSC Eights!

    Points: 927

    Paul White NZ (4/1/2010)


    Thanks, everyone. Glad I am not the only one to find this whole area fascinating.

    Yes, I do too [for reasons unfathomable]

    And I'm glad I kinda kicked off the discussion about when the backup knew when to stop. I'm slogging my way through all of Paul's postings, and drawing diagrams of my own.

    Again, thanks to all.

  • Teenu

    SSC Enthusiast

    Points: 199

    Good question! Tests ones' understanding in a better ways.

    Kudos to the Question submitter!

Viewing 6 posts - 61 through 66 (of 66 total)

You must be logged in to reply to this topic. Login to reply