Full backup Scenario

Question

Full backup Scenario

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

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

Peter Trast SSCarpal Tunnel Points: 4392 More actions · Answer 1

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 More actions · Answer 2

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: 64780 More actions · Answer 3

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: 150467 More actions · Answer 4

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

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Festeron SSC Eights! Points: 927 More actions · Answer 5

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 More actions · Answer 6

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

Kudos to the Question submitter!