What is the difference between NORECOVERY and RECOVERY when restoring database?
NORECOVERY specifies that roll back not occur. This allows roll forward 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 roll back should be performed after roll forward is completed for the current backup. Recovering the database requires that the entire set of data being restored (the roll forward set) is consistent with the database. If the roll forward set has not been rolled forward far enough to be consistent with the database and RECOVERY is specified, the Database Engine issues an error.
- Restore Sequence and Understanding NORECOVERY and RECOVERY
There is more to it. A database restore issued with NORECOVERY is left in a pending state and cannot be accessed. Logs and differentials can be added to the database while it is in this state since no independent activity is occurring on it.
RECOVERY places the database in an operational state. You cannot add any more components of a backup set to it at this time.