Post reply

Understanding the Undo Phase

  • February 15, 2013 at 3:48 am

    #289300

    Hi all

    I am a little confused about the undo phase of a restore in SQL. If a restore consists of the actual DB restore, followed by the redo phase to roll forward any committed transactions from the transaction log to a point in time if required, then why is an undo phase necessary?

    I had thought that any uncommitted transactions would only have been held in memory and as such during a restore these would not need to be rolled back?

    Thanks for any replies

  • February 15, 2013 at 6:27 am

    #1588284

    In a full and differential backup enough of the transaction log is backed up to ensure a consistent database upon restore. The SQL backup does not pick and choose the transactions that are backed up here. This means that uncommitted transactions will also be included. The UNDO phase rolls back those transactions to provide a consistent database upon completion of the restore.

  • February 15, 2013 at 7:19 am

    #1588308

    Kwisatz78 (2/15/2013)

    I had thought that any uncommitted transactions would only have been held in memory

    Nope. There's no requirements that uncommitted transactions not be written to disk, they can be, they often are. Hence in both restores and restarts SQL must run an undo phase to ensure consistency.

    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

  • February 15, 2013 at 7:28 am

    #1588318

    OK thanks to both for clearing that up, I will do more reading around checkpoints to.

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

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