Selecting the Appropriate Recovery Model for a Database in SQL Server 2005

I frequently receive the same question from my clients and from the audience at my seminars – “Which recovery model should I use on my SQL Server databases?”  Basically, there isn’t a right or wrong answer as each recovery model offers benefits and consequences.

It is important to select the appropriate recovery model because doing so affects an organization’s ability to recover, manage, and maintain data.For enterprise production systems, the Full recovery model is the best model for preventing critical data loss and restoring data to a specific point in time. As long as the transaction log is available, it is possible to even get up-to-the-minute recovery and point-in-time restore if the end of the transaction log is backed up and restored. The trade-off for the Full recovery model is its impact on other operations.

Organizations leverage the Simple recovery model if the data backed up is not critical, data is static or does not change often, or if loss is not a concern for the organization. In this situation, the organization loses all transactions since the last full or last differential backup. This model is typical for test environments or production databases that are not mission critical.

Finally, organizations that typically select the Bulk-Logged recovery model have critical data, but logging large amounts of data degrades system performance, or these bulk operations are conducted after hours and do not interfere with normal transaction processing. In addition, there isn’t a need for point-in-time or up-to-the-minute restores.For more information on recovery models review chapter 3, “Administering SQL Server 2005 Database Engine” on my  SQL Server 2005 Management and Administration Book.

Cheers…Ross Mistry