A seasoned Database Administrator knows the importance of routine backups of the SQL backups. These backups become helpful in the catastrophic situations when you have lost the complete database, but a healthy database backup can restore all these database content along with tables, relationships, dependencies, keys, procedures, triggers, etc.
An SQL database backup is essential in following situations.
- In accidental deletion
- When there is storage failure
- In natural disaster situation
- Hard drive sector holding SQL database file is corrupted
- In hardware failures
- Data loss during database mirroring, archiving etc.
The restore process involves copying the data from the backup files and reassigning the logged transactions to the data in a forwarding manner until it reaches the restore point. The restore point is the consistent state of the database before it had lost the saved tables.
In MS SQL Server, you can conduct the restore process using two methods –
RECOVERY and NORECOVERY are the two SQL commands that complete the database restoration. Both these commands work on the SQL Backups but the RECOVERY command uses both REDO and UNDO parts. Whereas the NORECOVERY command does not require the UNDO part and completes the process. Before starting the restoration, you should learn both methods to recover the database better.
Restore with RECOVERY.
The RECOVERY procedure is the default option for the backup database restoration. It requires the REDO and UNDO actions during restoration.
REDO.
The REDO feature applies the logged transaction to the data from the backup file and then forwards it in the database until reaching the recover point.
UNDO.
UNDO features works opposite to the REDO, it rollbacks the uncommitted transactions and makes the database available to the users.
Note: Restore WITH RECOVERY process only requires the UNDO feature if the REDO feature is not able to be consistent with the database. Otherwise the recovery process stops itself if the database is made consistent with the REDO feature.
Restore with NORECOVERY.
NORECOVERY is the better restoration method that helps the Administrator to restore the database from multiple backup files. While the running of the NORECOVERY method, the database remains in the ‘restoring’ state which means that it will remain inaccessible for the usage and the user cannot run queries in it. It helps the Administrator to complete the complete restoration and retrieve the full information.
You can complete the restore process with NORECOVERY with the help of several T-SQL Commands that you need to run differently as per your requirement.
Example – 1
WITH NORECOVERY
GO
RESTORE LOG FinanceDepartment FROM DISK = ‘E:\FinanceDepartment.TRN’
WITH RECOVERY
Go
The first command to restore the database FinanceDepartment will put the database in the restoring state only after restoring it and allowing the additional backups. The second command will restore the same database with the help of transaction logs.
Example – 2
In the second example, you will see the flexibility of the NORECOVERY command to run multiple additional backups at once and the transaction log with both NORECOVERY and RECOVERY.
WITH NORECOVERY
GO
RESTORE LOG FinanceDepartment FROM DISK = ‘E:\FinanceDepartment.TRN’
WITH NORECOVERY
Go
RESTORE LOG FinanceDepartment FROM DISK = ‘E:\FinanceDepartment.TRN’
WITH RECOVERY
GO
After completing the restoration, you need to make the database online again and you can do it by running the RECOVERY process easily.
GO
The command will make the database online again from the restoring state.
SQL Administrators can also use the Microsoft SQL Server Management Studio to restore database with NORECOVERY command.
Select the Databases option, right-click on it and select the Restore Database option. Then click on Options from the left panel on the Restore Database page, select Restore with NORECOVERY option and click on OK.
Limitations of the default restoration WITH RECOVERY
As we mentioned earlier that WITH RECOVERY is the default setup given in the SQL Server. So, it requires only a single command to restore the database.
WITH RECOVERY
GO
Differences between RECOVERY and NORECOVERY Options
- Recovery and NoRecovery both of these options need a SQL database backup. Recovery and NoRecovery are two command parts during the database restoration.
- The Recovery option will help if you have a single backup and if you want to recover directly. This recovery process, by default, involves both undo and redo parts, and no excess backup restoration is allowed. The RECOVERY option puts the database in the operational state.
- NORECOVERY advances the process so that the next action can be performed. If you have different types of backups (differential, transactional, etc) the NORECOVERY option should be the suitable choice.
Points to remember before starting the restoration
Before starting the restoration process using either the WITH RECOVERY or NORECOVERY option, you need to give your focus to some points that affect the quality of restoration.
- If you want to use the NORECOVERY process, then you should know that it will place the database in the restoring state and no longer available for use. So, you should plan it in advance.
- Depending on the severity of the corruption in the database, you should plan that whether you require full back up or partial backup.
- For a large size database, it is not easy to adjust the recovered data easily and it can create further problems. So, you can either increase the storage size or store the recovered data in the offline folder.
Conclusion
Both the manual restore processes provide only limited functionality to restore a large-sized database. They can take the help of backup files only if the corruption is not severe and if the whole database is corrupt, then you should not use these processes. Only a professional SQL Recovery software can handle deep corruption and recover the whole database. The benefit of using professional software is that it can provide you the option to restore the selected tables at a live SQL Server.
