How to Restore with Recovery Option in SQL Server

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.Working Restore With Recovery Option In SQL Server

Restore With Recovery in SQL Server

Restore With Recovery In SQL ServerOne 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.

Author Introduction:

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/

Comments are closed.