Choosing the Recovery Model for a Database

The simple recovery model is generally appropriate for a test or development database. However, for a production database, the best choice is typically the full recovery model, optionally, supplemented by the bulk-logged recovery model. However, 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.

To decide upon the optimal recovery model for a particular database, you should consider both the recovery goals and requirements for the database and whether you can manage log backups.

To select the best suited recovery model for a database, consider the recovery goals and requirements for the database. Answering the following questions can help you determine the availability requirements and the sensitivity to data loss.

Recovery Requirements

How important is it to never lose a change?

How easy would it be to re-create lost data?

Do you have two or more databases that must be logically consistent?

If this is so, consider using Microsoft Distributed Transaction Coordinator (MS DTC) transactions. For more information, see MS DTC Distributed Transactions.

Note

Under the full recovery model, if transactions have been marked in each of the related databases, you can recover the database to a consistent point. This requires restoring each of the databases with the same transaction mark as the recovery point for each. However, using a mark for a recovery point loses any transactions committed after that point. For more information, see Using Marked Transactions (Full Recovery Model).

Staffing Considerations

Does your organization use system or database administrators? If it does not, who will be responsible for performing backup and recovery operations, and how will the individuals be trained?

Data Usage Patterns

For each database, consider the following questions:

How frequently does the data in the database change?

Are some tables modified significantly more frequently than other tables?

Are there critical production periods? If there are, what are the usage patterns during these periods? Does the database experience peak periods for insert and other update operations?

You might want to schedule data backups to occur during off-peak hours. When the I/O system is under heavy use, typically, only log backups should be used.

Is the database subject to risky updates or application errors that may not be detected immediately?

If the database is, consider using the full recovery model. This lets you use log backups to recover the database to a specific point in time.

The bulk-logged recovery model is intended strictly as an adjunct to the full recovery model. We recommend 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.

Is the database subject to periodic bulk operations on the database?

Under this recovery model, most bulk operations are only minimally logged. If you use the full recovery model, you can switch temporarily to the bulk-logged recovery model before you perform such bulk operations. For information about what operations are minimally logged under the bulk-logged recovery model, see Operations That Can Be Minimally Logged.

Generally, the bulk-logged recovery model resembles the full recovery model, except that it minimally logs most bulk operations. A transaction log backup captures the log and, also, the results of any minimally logged operations that have completed since the last backup. This can make the log backups very large. Therefore, the bulk-logged recovery model is intended only for use during bulk operations that allow for minimal logging. We recommend that you use the full recovery model the rest of the time. As soon as a set of bulk operations finishes, we recommend that you immediately switch back to the full recovery model.