Paul White NZ (3/31/2010)

---

Tom.Thomson (3/29/2010)

---

There are a couple of additional things backup could do to improve performance: at some point before starting to write pages to backup media, it could write all dirty pages from cache to disc - this could be done either before or after (preferably before) working out the log start position; and it could write extra pages of the log to disc after the log stop position - either to the end of the log, or up to the last log entry that has commited a transaction. But there are a couple of issues with these "performance enhancements": (a) if pages are being dirtied fast enough the first trick will slow backup by more than in speeds recovery, so not much point to it; (b) extra log data after the stop point is all very well but theoretically with the end point defined either of the ways suggested it's possible that the log is growing fast enough that the backup will never terminate (and there's certainly some practical risk that it will take an excessive time to terminate). So MS may have kept it simple or may have done calculations which tell them which way will generally be best to go; only someone who knows their implementation of backup inside out will know what they have actually chosen to do (I certainly don't).

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