SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Full backup Scenario


Full backup Scenario

Author
Message
Peter Trast
Peter Trast
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1738 Visits: 655
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
Tom Thomson
Tom Thomson
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50688 Visits: 13159
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
Hugo Kornelis
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34350 Visits: 13120
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: http://sqlblog.com/blogs/hugo_kornelis
Paul White
Paul White
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79742 Visits: 11400
Thanks, everyone. Glad I am not the only one to find this whole area fascinating.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Festeron
Festeron
Mr or Mrs. 500
Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)

Group: General Forum Members
Points: 565 Visits: 230
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
Teenu
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 26
Good question! Tests ones' understanding in a better ways.

Kudos to the Question submitter!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search