Difference Between Restore with Recovery & Restore with Norecovery

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.

Calculating DTU's for Azure SQL Database

One of my clients asked to calculate the DTU’s required for an existing database before migrating to Azure SQL Database. Pricing Azure SQL Database is different from traditional SQL Server licensing. Azure SQL Database pricing depends on the database service tier options ,such as Database Transaction units and the maximum size of the database.

Azure SQL Database offers Basic, Standard, Premium, and Premium RS service tiers for both single databases and elastic pools. The cost of Azure SQL Database directly relates to which tier and performance level you are using. To determine service tier and performance level and database throughput units (DTUs) with the help of Azure database DTU calculator.

Read My Complete Article  posted in SQLServercentral.com "Here" 

Configure Read only routing for an always on availability group

One of my friend sent an email with the requirement to load balance the traffic coming from application to secondary SQL Server replicas in always on availability group. Read-only routing refers to the ability of SQL Server to route qualifying read-only connection requests to an available Always On readable secondary replica. The advantage of Setting the readable secondary as Read-Intent Only will allow the secondary server to serve the read-only workload only if the connection string of the application contains Application Intent=Readonly parameter. 

Read My Complete Article  posted in SQLServercentral.com "Here" 

Ganapathi varma Chekuri

Email: gana20m@gmail.com

Linkedin 

How to Change the File Extension for a Data File

Link Below:

How to Change the File Extension for a Data File

In general, the file extensions that we use for SQL Server data files are MDF (Primary Data File), NDF (Secondary Data File - this is optional), and LDF (Transaction Log File). SQL Server works fine with any file extension but Microsoft recommends we use MDF, NDF and LDF. 

But, a different file extension also work with SQL Server.

This article helps in finding a way to rename the file extensions.

Changing the Path for the Master Database

Link Below:

Changing the Path for the Master Database

This article will show how we can move the master database to a new location.

The ALTER DATABASE command would work for moving all the user databases and some system databases, including model, msdb and tempdb, but changing the path of the master database required a different strategy.