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.

Identifying Recovery Goals and Requirements

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.

When to Use the Simple Recovery Model

Use the simple recovery model if the following are all true:

• 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.

For more information, see Backup Under the Simple Recovery Model.

When to Use the Full Recovery Model

Use the full recovery model and, optionally, also the bulk-logged recovery model if any one of the following is true:

• 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.

For more information, see Backup Under the Full Recovery Model.

When to Use the Bulk-Logged Recovery Model

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 bulk logged under the bulk-logged recovery model, see Minimally Logged Operations.
  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.

For more information about the bulk-logged recovery model, including its restrictions, see Backup Under the Bulk-Logged Recovery Model.

For more information about the restore operations that are supported by the different recovery models, see Overview of Restore and Recovery in SQL Server.

Viewing and Changing the Recovery Model of a Database

A new database inherits its recovery model from the model database. The default recovery model of the model database depends on the edition of SQL Server. But this can be changed by anyone that has ALTER permission on the database. For information about how to view the current recovery model of the model database, see How to: View or Change the Recovery Model of a Database (SQL Server Management Studio).

To view or change the recovery model of a database

• How to: View or Change the Recovery Model of a Database (SQL Server Management Studio)
• sys.databases (Transact-SQL)
• ALTER DATABASE (Transact-SQL)

To switch recovery models

• Considerations for Switching from the Simple Recovery Model
• Considerations for Switching from the Full or Bulk-Logged Recovery Model

See Also

Concepts

Choosing a Recovery Model for Index Operations
Overview of the Recovery Models
Transaction Log Truncation

Other Resources

Automating Administrative Tasks (SQL Server Agent)
Disaster Recovery

Help and Information

Getting SQL Server 2005 Assistance

Change History