Every tech-conscious person is familiar with restoration process in the world of computing. This process is usually followed by the process of backup. In case of SQL Server, restoration procedure means copying the backup and move it to the recovery point. SQL Database recovery involves rolling forward uncommitted transactions and make the database online. However, SQL database restoration process can be done in two ways, “with recovery” and “with norecovery”. In this post, we will learn about the difference between restore with recovery and restore with norecovery. We also know the full procedure of these two approaches.
Difference between With Recovery and With No Recovery in SQL Server
“With recovery” and “NoRecovery” are actually the part of command that we apply during database restoration.
- Restore With Recovery in SQL Server
This recovery process by default involves both undo and redo parts while no excess backup restoration is not allowed. If redo/ roll forward is consistent with the database, smooth data recovery is possible by making the SQL database online. Undo part will not appear if the redo has not been made enough to be consistent with database. This may even stop the process of recovery.
- Restore With Norecovery in SQL Server
When restore with norecovery is used, the uncommitted transactions are kept by removing the undo part. Thus, it permits rolling forward to continue with next sequential statement and sequence restoration can let other backup restorations to take place to further roll forward the database. Restore with norecovery can sometimes roll forward data to where the database consistency is maintained.
SQL Server Restore With Recovery vs NoRecovery
Now that we know the difference between these two processes, Let’s go through the detail of those.
- SQL Server Restore With Recovery
It is one of the many available options for database restoration. This makes the restored database accessible to the users. Restore with recovery is the default option if you are restoring a full backup and not multiple backups.
Use T-SQL Statements to Perform the Restoration
These T-SQL scripts can be used for applicable situations:
For Full Backup Restoration
Though this will be the automatic option for restoring a full backup, specify it with this command:
RESTORE DATABASE MotionWorks FROM DISK = 'C:\MotionWorks.BAK' WITH RECOVERY GO
Recovering a ‘Restoring’ State Database
The following script is used when we have to recover a database that is still in the restoring state If this script is used, every user including the end users can access the database.
RESTORE DATABASE MotionWorks WITH RECOVERY GO
Use “With Recovery” in Case of Last Backup while Restoring Multiple Backups
The last backup restoration in the case of multiple backups will require with recovery command. Use this following T-SQL Script to do that:
RESTORE DATABASE MotionWorks FROM DISK = 'C:\MotionWorks.BAK' WITH NORECOVERY GO RESTORE LOG MotionWorks FROM DISK = 'C:\MotionWorks.TRN' WITH RECOVERY GO
- SQL Server Restore With NoRecovery
We see the use of No Recovery command when there is the requirement of multiple backup restoration. In that case, With Norecovery option is used in all instances except the last one. Since With Norecovery command enables ‘restoring state’ in a database, additional backups can be performed. In the meantime, users are prohibited from accessing that database.
Restore database with norecovery in sql server
Here are some examples of commands that need to be used
Restore Full Backup With Norecovery and One Transaction Log Backup
Here, Norecovery command has been used to perform additional backups later. This command will restore the database but that database will be in the restoring state. The second command is used for transaction log backup restoration using With Recovery and making the database online for end users.
Restore Full Backup With Norecovery and Two Transaction Log Backups
The first two commands will restore the database and log where we have used With NORECOVERY option. The last command “With Recovery” will restore the transaction log.
Using SQL Server Management Studio
With the help of SQL Server Management Studio, Restore With Norecovery command can be used. Click on Options-->Choose Restore With Norecovery and click OK.
Final Words
In this post, we have discussed the difference between restore with recovery and restore with norecovery. We have also shared the various commands in detail related to these two types of database restoration processes. Users should get a clear idea about these two concepts and be able to restore SQL database using both the approaches.
