Restore with NORECOVERY

Chaitanya Kiran 696 Reputation points
2021-10-13T12:46:47.393+00:00

I read in an article that If the database is being restored with NORECOVERY, the Undo phase is skipped. I would like to know why it is skipped and what will happen to the uncommitted transactions.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,790 questions
0 comments
Accepted answer
  1. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2021-10-14T01:13:50.367+00:00

    Hi @Chaitanya Kiran ,

    >So, the database comes online before UNDO phase when restored with recovery?

    No, the RESTORE Database WITH NORECOVERY option puts the database into a ‘restoring state’, and no users can access the database in this state.

    > I would like to know why it is skipped and what will happen to the uncommitted transactions.

    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. Then the undo phrase is not occurred, and the database is in a ‘restoring state’, until you restore the last log backup with recovery, which include the undo phrase.

    If the user is restoring a database using multiple backup files, NO RECOVERY option is used for each restore except the last. The RESTORE Database WITH NORECOVERY option puts the database into a ‘restoring state’ so that additional backups can be restored and no users can access the database in this state. If user wants to recover database without using additional backups, RESTORE Database WITH RECOVERY option will be used to bring database online again making it usable for users.

    Refer to the blog SQL Server Restore Database With Norecovery.

    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ronen Ariely 15,096 Reputation points
    2021-10-13T13:35:38.527+00:00

    Good day Chaitanya,

    When you do a Restore with NORECOVERY it means that the database is yet ready to be used. This is usually only a step when you have multiple transaction log backups that needed to be restored one after the other. Once you are ready and you restored the tail of the log, then you do the RECOVERY, which include the three steps (1) Analysis Phase. (2) Redo Phase and (3) Undo Phase.

    So it is true that during the Restore with NORECOVERY the Undo phase is not done, but it will be run once you do the RECOVERY

    https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-and-recovery-overview-sql-server?view=sql-server-ver15#TlogAndRecovery

    1 person found this answer helpful.
  2. Tom Phillips 17,716 Reputation points
    2021-10-13T13:28:27.617+00:00

    It is "skipped" for that restore. It is delayed until the next restore is complete. Then the undo is run.

    0 comments