What is the Importance of the NORECOVERY and STANDBY Options?
What is the Importance of the NORECOVERY and STANDBY Options?
Ask by Steilla Mitchel
Updated 04 Sep 2023
The NORECOVERY and STANDBY options are used in the RESTORE DATABASE statement in SQL Server to control how the database is restored.
The NORECOVERY option tells SQL Server to not roll back any uncommitted transactions when the database is restored. This means that the database will be in a restoring state and will not be available for use until the next restore operation is performed. The NORECOVERY option is typically used when you are restoring a database to a point in time before a failure and you do not want to lose any data.
The STANDBY option tells SQL Server to roll back any uncommitted transactions when the database is restored. However, the database will be put into a read-only state so that users can still read the data. The STANDBY option is typically used for disaster recovery purposes. It allows you to restore a database to a secondary server so that you can still access the data in the event of a failure on the primary server.
The following table summarizes the differences between the NORECOVERY and STANDBY options:
The choice of which option to use will depend on your specific needs. If you need to restore a database to a point in time before a failure and you do not want to lose any data, then you should use the NORECOVERY option. If you need to restore a database for disaster recovery purposes, then you should use the STANDBY option.