The Restore with Recovery option available in SQL Server for restoring backups is explained in detail, along with T-SQL codes used in its implementation.
Using SQL Server can be very tasking if you are not clear with its feature set, and what all to do with it. SQL Server is one of those software that floods the user with lots of options, features, commands etc. Apart from the bulk of data it allows you to store, it also gives you the option of backing it up. And beyond backing up the SQL databases, you also have multiple options for restoring the completed backups. Other than the different backup options provided by SQL Server, it also provides you with different restore options. Today we will discuss the RESTORE WITH RECOVERY option.
Restore With Recovery in SQL Server
One of the many restoration options available in SQL Server include the Restore with Recovery option, what it mainly does is to make the database available for use to allow users to get access to the restored version.
When using this restore option, if you give commands like RESTORE DATABASE, RESTORE LOG, then the WITH RECOVERY option will be automatically implemented. It is by default set to work with these commands, and does not need any special command to be activated.
If you have just taken a full backup, and need to restore it, the RESTORE WITH RECOVERY command will be activated by default. And once the restoration is done, you can begin using the data. If instead of a full backup, you are restoring through multiple backup files, the WITH NORECOVERY option should be used. This needs to be done for each restore you make, sparing the last one.
If the restore process for a database has not been completed, and you wish to recover it already, without all the additional backups, you should use the RESTORE DATABASE WITH RECOVERY option. This will put the database online to allow all users to access it.
T-SQL Statements for executing these options
The T-SQL script for all the recovery options mentioned above, have been explained below.
Using WITH RECOVERY to Restore Full Backups
As explained already, when you want to restore a full backup, this option will be activated by default, to specify it, use the following code:
RESTORE DATABASE MotionWorks FROM DISK = 'C:\MotionWorks.BAK' WITH RECOVERY GO
Recovering a ‘Restoring’ State Database
To recover a database that is still not out of the restoring state, you can make use of the command given below. This will also help you make the database available for all users, including the end users.
RESTORE DATABASE MotionWorks WITH RECOVERY GO
Restoring Multiple Backups and Applying WITH RECOVERY to Last backup
When the very first restore is done, the NORECOVERY option is used to allow additional restores. On the second command all the transaction logs will be restored, bringing the database online to be used by end user. Make use of the following T-SQL Script for the same.
RESTORE DATABASE MotionWorks FROM DISK = 'C:\MotionWorks.BAK' WITH NORECOVERY GO RESTORE LOG MotionWorks FROM DISK = 'C:\MotionWorks.TRN' WITH RECOVERY GO
For avoiding any data loss, invest in a powerful SQL Server repair tool as a failsafe.
Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including fix mdb and sql recovery software products. For more information visit https://www.datanumen.com/