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

Summary: This blog will discuss how to restore a database with RECOVERY and NORECOVERY options. Restoring the db with RECOVERY option is the default option for users with FULL backup. However, if you have different types of backups (differential, transactional, etc.) you may need to use the NORECOVERY option to combine the backups. You may also try restoring SQL database to its original form using Stellar Repair for MS SQL software. Download the software demo version to preview the recoverable db objects.

To prevent data loss and recover a SQL Server database from disaster, a DBA has to restore database backups. When it comes to restoring a database from backup, there are two main options the DBA can use: ‘With RECOVERY’ and ‘With NORECOVERY’.

How to Recover SQL database With RECOVERY and with NORECOVERY?

Requirements

Before performing recovery of SQL Server database using the RECOVERY and NORECOVERY options, you must have the following installed on your system.

Getting started

Before restoring the db, make sure you create backup of the database first. You can create the backup using SSMS or T-SQL.

Steps to Create SQL Database Backup using SSMS

Note: You can also perform other types of backups such as differential and transactional.

Steps to Create SQL Database Backup using T-SQL in SSMS

You can generate T-SQL backup script to perform a backup task using SSMS. To do it, follow these steps:

To automate the backup process instead of configuring it manually, execute the following T-SQL code:

BACKUP DATABASE [sales] TO  DISK = N'c:\sql\sales.bak' WITH NOFORMAT, NOINIT,  NAME = N'earnings-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

This option will allow creating the backup in a Job. A job is a group of operations you can run in the SQL Server Agent. The SQL Server Agent is a SQL Server component with its own service used to run jobs.

SQL Server Restore With Recovery and with NORECOVERY

Let’s discuss both these options in detail:

RECOVERY Option

Recovery is the default option. This option does a rollback and a roll forward. Use this option when you need to restore a database from a FULL backup. Follow these steps to restore database with recovery in SQL Server 2008 and above versions:

RESTORE DATABASE [earnings] FROM  DISK = N'c:\sql\earnings.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5
, RECOVERY 
GO

In this example, it is not necessary to use the RECOVERY argument which is the default one, but I added it to understand the difference compared with the other option.

With NORECOVERY Option

Use the NORECOVERY option when you need to restore multiple backups. It puts the database in ‘RESTORING’ state to prevent users from accessing the database unless additional backups are restored. Restore with NORECOVERY is used for restoring each database backup except the last. The last backup can be restored using the RECOVERY option to bring the db online for usage.

For example, if you need to restore a full database backup followed by a transaction log backup, the full backup will be restored with NORECOVERY argument and then the log backup will be restored with recovery. Here’s how to do it:

RESTORE DATABASE [earnings] FROM  DISK = N'c:\sql\earnings.bak' WITH  FILE = 2,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [earnings] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\earnings_LogBackup_2018-12-21_12-24-25.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5, RECOVERY 

Note: In the above query, the first command restores the db with NORECOVERY option, and the second command restores the log backup using the RECOVERY option.

Other Options to Restore the Data

If you do not have a backup or if it is damaged, you can use SQL Recovery software, such as Stellar Repair for MS SQL to restore your data. The software helps repair a damaged (corrupt) SQL database and restore it to its original state. You can download the software from here:

To use this software, you just need to select the MDF file and repair it. Once the file is repaired, you can use it to restore your SQL database.

For detailed steps to restore the database using Stellar Repair for MS SQL software, refer to this link.

Conclusion

The blog outlined the steps to create backups manually using SSMS and T-SQL code. It also discussed the process restore an MS SQL database using the RECOVERY and NORECOVERY options. Additionally, it explained using the Stellar Repair for MS SQL software to repair a damaged SQL Server database. If you have questions, feel free to ask in the comment box below.

Related Post

Exit mobile version