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.
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.
The Restore command either ends after the redo part or continues until the undo part, depending on whether specified WITH RECOVERY OR NORECOVERY.
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.
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.
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:
Restore full backup WITH NORECOVERY and one transaction log backup
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
The first and second command restores the database and log using NORECOVERY option and the last command restores the transaction log using RECOVERY option.
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.
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.