SQL Server Restore Database With Norecovery

Andrew Jackson | Published: December 11, 2018 | Backup |

Overview

In SQL Server, the process of copying data from a backup & applying logged transactions to the data rolling it forward to target recovery point is known as restore process. Each backup contains sufficient log to roll back uncommitted transactions to bring consistent and usable state of database. This process of rolling forward uncommitted transactions and making database online is known as recovery. The blog will be discussing procedure to restore Database with Norecovery in SQL Server.

Roll Forward (Redo) and Roll Back (Undo) in Restore Process

Redo is the technique of applying logged transactions to data copied from the backup to roll forward that data in a recovery point. At this state, database is in unusable state & has uncommitted transaction. To perform roll forward, Database Engine of SQL Server processes log backups as they are restored, starting with log present in full backups.

Undo is needed as part of recovering the database. It will roll back any uncommitted transactions and will make the database available to users. After rollback, subsequent backups cannot be restored. Uncommitted transactions are undone by being rolled back and if data is already transactionally consistent at the beginning of recovery process, undo phase is skipped. Recovery brings the database online after the database is transactionally consistent.

Difference Between RECOVERY and NORECOVERY while Restoring Database

The Restore command either ends after the redo part or continues until the undo part, depending on whether specified WITH RECOVERY OR NORECOVERY.

WITH RECOVERY

Recovers the database using both redo and undo parts and additional backups cannot be restored. This is the default option. If roll forward (redo) has not been rolled forward enough to be consistent with database, undo part cannot occur. Error may be generated and stop the recovery process. If roll forward is consistent with the database, recovery can be performed smoothly making the database online.

WITH NORECOVERY

It removes the undo part to keep the uncommitted transactions. Omitting the undo part allows roll forward to continue with next statement in the sequence and restore sequence can restore other backups to roll the database further forward in time. Sometimes, RESTORE WITH NORECOVERY rolls forward data to where it is consistent with database.

RESTORE Database WITH NORECOVERY

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.

Ways to use RESTORE Database WITH NORECOVERY are:

Using T-SQL

Restore full backup WITH NORECOVERY and one transaction log backup

Restore full backup WITH NORECOVERY

The first restore uses the NORECOVERY option so additional backups can be done. It does the restore and leaves the database in a restoring state. The second command restores the transaction log backup using RECOVERY and then brings the database online for usage.

Restore full backup WITH NORECOVERY and two transaction log backups

Transaction Log Backups

The first and second command restores the database and log using NORECOVERY option and the last command restores the transaction log using RECOVERY option.

Restore full backup WITH RECOVERY

Restore full backup WITH RECOVERY

It is the default option of restoring database. It uses RECOVERY Option leaving the database online and additional logs cannot be restored.

Restore a Database that is in ‘restoring’ state

Restoring

This command is used for database that is in ‘restoring’ state and to make it online available for use.

Using SQL Server Management Studio

We can use RESTORE with NORECOVERY option using SQL Server Management Studio. Go to Options Choose the recovery state desired i.e. Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored. [RESTORE WITH NORECOVERY] and Click OK.

Conclusion

The blog has been aimed to explain the ways of restoring database with multiple options. Restoring database can be in three ways, with Recovery, with Norecovery and with standby options. The blog discusses mainly about the restore database with Norecovery option that does not roll back any uncommitted transactions and leaves database non-operational. This option can be used either by using T-SQL commands or by using SQL Server Management Studio.