backups... recovery to start of backup time, or end.

  • Was having a discussion with a colleage and just want to make sure I was telling him the correct informatino...

    If I have a database which is in simple recovery mode and start a backup at 12pm... then while the backup is running, you perform some transactions- lets say at 12:30pm, then the backup completes at 1pm- that backup will include the 12:30pm transactions since "Each data backup includes part of the transaction log so that the backup can be recovered to the end of that backup. "

    correct or incorrect?

  • correct - unless I've been getting it wrong all these years 🙂

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • The end of the backup process marks transactions as rollforward or rollback depending on if they're completed or not when the backup finishes. So you can get both situations, the ones where it captures those changes and the ones where it doesn't capture the changes because they weren't complete.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • gotcha- that makes sense... essentially I was trying to explain (enthusiastically) that even in simple mode, the transaction log is used during the full backup to track changes made since the backup started. My colleague was under the mistaken impression that a backup which starts at 12pm is essentially a snapshot of the database state at 12pm....

    He is a good arguer- so I questioned myself for a moment... but I also reviewed this is the MS training kit for 70-432 which describes how this is accomplished:

    to paraphrase ,it bascially says that sql server does the following:

    "1.)locks the database blocking all transactions

    2.)places a mark in the transaction log

    3.)releases the lock from step 1

    4.)extracts all data pages and writes to the backup device

    5.)locks the database again

    6.)places another mark in the transaction log

    7.)releases the lock from step 5

    8.) extracts the portion of the log between the marks and appends it to the backup.

    "

  • It's consistent as of the end of the data-copying portion of the backup. Not necessarily the end of the backup, thought usually close to it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

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