Summary: In this problem tackling blog we are going to discuss how to perform SQL Server restore database with recovery vs no recovery option. The user can try the manual ways to restore the database but in case if you are facing a problem then take the help of SQL Backup recovery to restore the database from the .bak files of SQL Server database.
With regards to testing and the basic job of a DBA which is one of the basic and weak activity exercises, at that point, it is restoring the database from Disaster. Furthermore, when we after stating this, we make certain about the psychological weight a DBA needs to encounter when the entire creation gets hampered.
In this new blog, we will tell the best way to restore a database with the RECOVERY and NORECOVERY choice. The recovery choice will help on the off chance that you have a single backup and you need to recover legitimately. Notwithstanding, in the event that you have various kinds of backups (differential, transactional, and so on,) you may need to utilize the NORECOVERY option to combine the backups.
Important Note: If you are having corrupted Backup files then try SysTools SQL Backup Recovery software to restore data from corrupted SQL .bak files. This software supports all versions of Windows OS and SQL Server 2019 and below versions.
So as to figure out how to restore, we will make the backup:
How to Make Backup in SQL Server Database
First, open the SSMS and right-click the database and choose Task>Back Up
There are different sorts of backups, for example, differential and transactional. While taking a backup, you can indicate where you need to store the backup of your database.
It is conceivable to backup using T-SQL. To do it, you can utilize the script option.
This T-SQL code assists with automating tasks as opposed to configuring it manually.
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
Click on the Execute button.
The code stores the backup in the c:\sql drive and the file name is xyz.bak
SQL Server Restore Database with Recovery vs No Recovery Options
At the point when you restore a database, you have the option to restore with recovery and with no recovery.
Recovery is the default choice. This choice does a rollback and a move forward. At the point when you have to restore a database, yet you don’t have to perform more rebuilding efforts later.
WITH RECOVERY OPTION
You can recovery in SSMS by right-clicking Databases and choosing the Restore Database option.
Select the database that you need to restore and the backup accessible
In the options page, you can choose the recovery state. In this situation, we have only one backup, so the recovery state will be with RESTORE WITH RECOVERY
With NORECOVERY OPTION
At the point when you have to restore a gathering of backups of the database, the NORECOVERY option is helpful. For instance, on the off chance that you have to restore utilizing a full back up and after an transactional backup, the full backup will be restored with NORECOVERY script and afterward, the backup will be restored with recovery.
The accompanying choice tells the best way 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
Another Option to Restore the Data
So far we have discussed SQL Server Restore Database with Recovery vs No Recovery methods If your SQL Server database is damaged, you have different options to restore your data uniquely in contrast to using the backup. The user can take the help of SysTools SQL Backup Recovery. By using this software the user can easily preview and recover the corrupt .bak file of the SQL Server database. Also, it allows the user to preview and recover deleted SQL Server database components. This Advanced application supports SQL Microsoft SQL Server 2019 and below version.
Conclusion
Also Read: Best SQL Recovery Software for 2020
In this article, we figured SQL Server Restore Database with Recovery vs No Recovery methods. We additionally learned elective tool to restore a corrupted SQL Server database. Moreover, we figured out how to make backups manually or utilizing T-SQL code to automate the task of backup.