Introduction

Backing up the database in MS SQL Server is vital to safeguard and recover the data in case of scenarios, like hardware failure, server failure, database corruption, etc. MS SQL Server provides different types of backups, such as differential, transactional, and full backup. A full backup allows you to restore the database in exactly the same form as it was at the time of creating the backup. The differential backup stores only the edits since the last full backup was created, whereas the transaction log backup is an incremental backup that stores all the transaction logs.

When you restore SQL database backup, SQL Server offers two options to control the state of the database after restore. These are:

RESTORE WITH RECOVERY

When you use the RESTORE WITH RECOVERY option, it indicates no more restores are required and the state of database changes to online after the restore operation.

RESTORE WITH NORECOVERY

You can select the WITH NORECOVERY option when you want to continue restoring additional backup files, like transactional or differential. It changes the database to restoring state until it’s recovered.

Now, let’s learn how to use the WITH RECOVERY and NORECOVERY options when restoring the database.

How to Restore MS SQL Server Database with the RECOVERY Option?

You can use the WITH RECOVERY option to restore a database from full backup. It is the default option in the Restore Database window and is used when restoring the last backup or only the backup in a restore sequence. You can restore database WITH RECOVERY option by using SQL Server Management Studio (SSMS) or T-SQL commands.

1. Restore Database with RECOVERY Option using SSMS

If you want to restore database without writing code and scripts, then you can use the graphical user interface in SSMS. Here are the steps to restore database WITH RECOVERY using SSMS:

Open SSMS and connect to your SQL Server instance.
Go to Object Explorer, expand databases, and right-click on the database.
Click Tasks > Restore.

SSMS interface showing database selection, Tasks menu, and Restore option

On the Restore database page, under General, select the database you want to restore and the available backup.
Next, on the same page, click Options.
In the Options window, select the recovery state as RESTORE WITH RECOVERY. Click OK.

SSMS Options window with RESTORE WITH RECOVERY selected

2. Restore Database with RECOVERY Option using T-SQL Command

If you have a large number of operations that need to be managed or you want to automate the tasks, then you can use T-SQL commands. You can use the below T-SQL command to restore the database with the RECOVERY option.

RESTORE DATABASE [DBName] FROM DISK = 'C:\Backup\DB.bak' WITH RECOVERY;

How to Restore MS SQL Server Database with NORECOVERY Option?

You can use the NORECOVERY option to restore multiple backup files. For example, if your system fails and you need to restore the SQL Server database to the point just before the failure occurred, then you need a multi-step restore. In this, each backup should be in a sequence, like Full Backup > Differential > Transaction log. Here, you need to select the database in NORECOVERY mode except for the last one. This option changes the state of the database to RESTORING and makes the database inaccessible to the users unless additional backups are restored. You can restore the database with the NORECOVERY option by using SSMS or T-SQL commands.

1. Using T-SQL Commands

Here are the steps to restore MS SQL database with the NORECOVERY option by using T-SQL commands:

Step 1: First, you need to restore the Full Backup by using the below command:

RESTORE DATABASE [YourDatabaseName]
FROM DISK = N'C:\Path\To\Your\FullBackup.bak'
WITH NORECOVERY,
STATS = 10;

Step 2: Then, you need to restore the Differential Backup. Use the below command:

RESTORE DATABASE [YourDatabaseName]
FROM DISK = N'C:\Path\To\Your\DifferentialBackup.bak'
WITH NORECOVERY,
STATS = 10;

Step 3: Now, you have to restore the Transaction log backup (last backup WITH RECOVERY). Here’s the command:

RESTORE LOG [YourDatabaseName]
FROM DISK = N'C:\Path\To\Your\LastTransactionLogBackup.bak'
WITH RECOVERY,
STATS = 10;

2. Using SQL Server Management Studio (SSMS)

You can follow the below steps to restore the database with NORECOVERY option using the SSMS:

In SSMS, go to the Object Explorer, expand databases, and right-click the database node.
Click Tasks, select Restore, and click Database.
In the Restore Database page, select the source (i.e. full backup), and the destination. Click OK.
Next, add the information about the selected backup file in the option labelled - Backup sets to restore.
Next, on the same Restore Database page, click Options.
On the Options page, click RESTORE WITH NORECOVERY in the Recovery state field. Click OK.

SSMS Options page with RESTORE WITH NORECOVERY selected

What if the SQL Database Backup File is Corrupted?

Sometimes, the restore process can fail due to corruption in the database backup file. If your backup file is corrupted or you've not created a backup file, then you can take the help of a professional MS SQL repair tool, like Stellar Repair for MS SQL Technician. It is an advanced SQL repair tool to repair corrupt databases and backup files with complete integrity. The tool can repair backup files of any type - transactional log, full backup, and differential - without any file-size limitations. It can even restore deleted items from the backup database file. The tool is compatible with MS SQL Server version 2022, 2019, and earlier.

Conclusion

Above, we have discussed the stepwise process to restore the SQL database with RECOVERY and NORECOVERY options in MS SQL Server. If you face any error or issue while restoring the backup, then you can use a professional SQL repair tool, like Stellar Repair for MS SQL Technician. It can easily restore all the data from corrupt backup (.bak) files and save it in a new database file with complete precision. The tool can help resolve all the errors related to corruption in SQL database and backup (.bak) files.

Hamna Fayyaz

Recently published

How to Perform MS SQL Server Restore with RECOVERY and NORECOVERY Options