hi, what is the best recovery model to use for MS NAV?
thanks in advance.
That depends on what you want, and how much data you can afford to lose in case you lose the actual database. If you can afford to lose a week’s worth of data then set it to Simple, and schedule a weekly full backup. If it’s a day’s worth of data that you can afford to lose then Simple and schedule a daily full backup. If you want to lose no more than an hour of data (or 15 minutes) at the most then Full and put in place a more sophisticated backup plan that includes full and transaction log backups.
A recovery model is a database configuration option that controls how transactions are logged, whether the transaction log is backed up, and what restore options are available for the database. The recovery model you choose for your database has both data recovery implications and performance implications, based on the logging the recovery model performs or doesn’t perform.
SQL Server provides three recovery models: Full, Simple, and Bulk-Logged.
The Full recovery model is the default and the recommended recovery model, logging all operations andletting you recover to the point of failure. The Simple recovery model minimally logs most operations and doesn’t let you back up or restore the transaction log
Simple Recovery Model :-
- Point of failure recovery is unnecessary è If the database is lost or damaged, you are willing to lose all the updates between a failure and the previous backup.
- You are willing to risk losing some data in the log.
- You do not want to back up and restore the transaction log, preferring to rely exclusively on full and differential backups
Full Recovery Model :-
- You must be able to recover all the data.
- If the database contains multiple filegroups, and you want piecemeal restore of read/write secondary filegroups and, optionally, read-only filegroups.
- You must be able to recover to the point of failure.
- You want to be able to restore individual pages.
- You are willing to incur the administrative costs of transaction log backups.
Bulk Logged Recovery Model
The bulk-logged recovery model is intended strictly as an adjunct to the full recovery model. It is recommended that you use it only during periods in which you are running large-scale bulk operations, and in which you do not require point-in-time recovery of the database.
The simple recovery model is generally appropriate for a test or development database. For a production database, the best choice is typically the full recovery model. The simple recovery model is sometimes appropriate for a small production database, especially if it is mostly or completely read-only, or for a data warehouse
It is not a very pleasant scenario to discover after a database crash that your backups are corrupt or nowhere to be found or no one knows how to restore the backup(s)… So please test this scenario a couple of times until you feel confident about it
The objectives to consider in recovery are not only what to recover, but how long that recovery takes. That “how long” may involve more than just the restore time. A restore, with a data loss (even a small one), can result in the need to reconcile what is missing. Recovery should be considered as “recovery of operations”, not just recovery of the database.
The recovery objectives are defined as:
Recovery Point Objective (RPO)
This defines the timeframe for allowable data loss. The larger this time, then more time likely spent in reconstructing that data. No hard rules here. You need to decide what fits your needs.
Recovery Time Objective (RTO)
This defines how long recovery takes. Factors such as database size and system performance can play factors here. Database size and recovery performance are factors here. So is also staff training. Don’t help to have all those backups if no one knows how to restore them.