2

I would like to ask a question regarding recovery process while restoring database operation. Let's say I restored a full backup with NORECOVERY followed by restoring two t-log backups with NORECOVERY. Lastly, I am going to restore the last 3rd t-log backup with RECOVERY keyword.

The question is: when I restore the last t-log backup with RECOVERY keyword, does SQL Server perform redo as well as undo phases or just does undo phase?

It seems to me that when we restore backup with NORECOVERY keyword, SQL Server also performs redo phase. The same thing with restoring t-log backups with standby keyword. In this case, SQL Server creates a separate file with the name that contains only "undo" not any "redo".

Improve this question

1 Answer 1

Reset to default
7

The Comparison of RECOVERY and NORECOVERY section of the RESTORE Statements doc should help you with that question:

Rollback is controlled by the RESTORE statement through the [ RECOVERY | NORECOVERY ] options:

  • NORECOVERY specifies that rollback doesn't occur. This allows rollforward to continue with the next statement in the sequence.

    In this case, the restore sequence can restore other backups and roll them forward.

  • RECOVERY (the default) indicates that rollback should be performed after rollforward is completed for the current backup.

    Recovering the database requires that the entire set of data being restored (the rollforward set) is consistent with the database. If the rollforward set has not been rolled forward far enough to be consistent with the database and RECOVERY is specified, the Database Engine issues an error.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.