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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1074 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
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26195 Visits: 12506
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
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18969 Visits: 12426
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36128 Visits: 11361
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
Old Hand
Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)

Group: General Forum Members
Points: 315 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