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’.
Before performing recovery of SQL Server database using the RECOVERY and NORECOVERY options, you must have the following installed on your system.
Before restoring the db, make sure you create backup of the database first. You can create the backup using SSMS or T-SQL.
Note: You can also perform other types of backups such as differential and transactional.
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.
Let’s discuss both these options in detail:
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.
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.
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.
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.
Priyanka is a technology expert working for key technology domains that revolve around Data Recovery and related software's. She got expertise on related subjects like SQL Database, Access Database, QuickBooks, and Microsoft Excel. Loves to write on different technology and data recovery subjects on regular basis. Technology freak who always found exploring neo-tech subjects, when not writing, research is something that keeps her going in life.
Stellar Repair for MS SQL is an enterpriRead More
3-in-1 software package, recommended byRead More
Stellar Converter for Database is an effRead More
Powerful tool, widely trusted by users &Read More