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.
- Any SQL Server version
- SQL Server Management Studio (SSMS)
- Stellar Repair for MS SQL installer
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
- Open SSMS, right-click on Databases, and then click Tasks > Back Up
- As per the requirement, you can perform a full backup. For this, select ‘FULL’ from ‘Recovery model’ under the ‘Source’ section. Next, specify the path where you want to store the backup of your database.
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:
- Click the Script button to create the backup script in a new query window and then save it to a new file or in the clipboard. You can also create a scheduled backup job.
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
- If you need to take backup daily, it is possible to save the script action into a job:
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.
- Specify the ‘Name’ and ‘Owner’ for the job.
- 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 or monthly 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.
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:
- In SSMS, right-click Databases, and then select the Restore Database option:
- In ‘Restore Database’ window, select the database that you want to restore and the backup available:
- In the ‘Options’ page, select the recovery state as RESTORE WITH RECOVERY:
- Additionally, you can use T-SQL instead of the SSMS GUI. 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
GOIn 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.
