How to Perform MS SQL Server Restore with RECOVERY and NORECOVERY

When it comes to challenging and critical JOB role of a DBA which is one of the critical and vulnerable job activity, then it is restoring the database from Disaster in stimulated time.
And when we are saying this, we are sure about the mental pressure a DBA has to experience when the whole production gets hampered by this reason.

There are two main options to recover a database- RECOVERY and NORECOVERY.

In this new article, we will show how to restore a database with the RECOVERY and NORECOVERY option. The recovery option will help if you have a single backup and you want to recover directly. However, if you have different types of backups (differential, transactional, etc,) you may need to use the NORECOVERY option to combine the backups.

Requirements

The following requirements are necessary for this article:

1. Any SQL Server version installed.
2. The SQL Server Management Studio (SSMS) installed on the machine.
3. Stellar Repair for MS SQL installer.

Getting started

In order to learn how to restore, we will create the backup first. Open the SSMS and right click the database and select Task>Back Up:

As per the requirement, you can perform a full backup. There are other types of backups such as differential and transactional. While taking a backup, you can specify where you want to store the backup of your database.

It is possible to backup using T-SQL. To do it, you can use the script option and save the script generated in a new file, in the clipboard and action job or in a new query window:

The code generated will be like this:

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

The T-SQL code helps to automate tasks instead of configuring it manually. If you need to do the same tasks all the days, using the code can be useful. To do this you can use the T-SQL code and execute it:

If you need to take the backup on daily basis, it is possible to save the script action into a job:

This option will allow to create the backup in a Job. A job is a group of operations that 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.

The job contains the name and the owner:

The step of the job contains the T-SQL Script that runs the backup:

By default the backup runs in the master database and you can modify the T-SQL code:

You can also schedule the job to run at a specified time and day. For example, you can run the backup daily, montly at 9:00(Example)

The code stores the backup in the c:\sql drive and the file name is sales.bak. The database to back up is the ‘sales’ database. For more information about the other arguments, refer to this link.

Restore With Recovery and with NORECOVERY

When you restore a database, you have the option to restore with recovery and with no recovery.

Recovery is the default option. This option does a rollback and a roll forward. When you need to restore a database, but you do not need to perform more restorations later.

Let’s take a look to an example:

RECOVERY option

You can recover in SSMS by right-clicking Databases and selecting the Restore Database option:

Select the database that you want to restore and the backup available:

In the option page, you can select the recovery state. In this scenario, we have just one backup, so the recovery state will be with recovery:

Additionally, you can use T-SQL instead of the UI. This is useful if you want to automate the tasks:

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

When you need to restore a group of backups of the database, the NORECOVERY option is useful. For example, if you need to restore using a full backup and after a transaction backup, the full backup will be restored with NORECOVERY argument and then the backup will be restored with recovery.

The following option shows 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 that the first 2 lines are executed to a restore with NORECOVERY, and the third line until the sixth lines are executed to a RECOVERY.

Other options to restore the data

If your SQL server database is damaged, you have other options to restore your data different than using the backup. Or if you do not have a backup or it is damaged, you have this SQL Recovery software to restore your data.

Stellar Repair for MS SQL

This SQL Recovery software allows you to repair a damaged database. You can download the software here:

To use this software, you just need to find the MDF file. That is the primary data file that stores the database information in SQL Server. You will need to stop the MS SQL Server service and then you can restore the file:

Conclusion

In this article, we learned how to restore a database using MS SQL using the RECOVERY and NORECOVERY option. We also learned alternative tools to restore a damaged SQL Server database. In addition, we learned how to create backups manually or using T-SQL code to automate the task of backup. If you have questions, feel free to ask.