Recovery Models: Selecting and Switching

A recovery model is a database configuration option that controls transaction log maintenance. The recovery model basically specifies how SQL Server manages logs files and controls what kind of backup and recovery procedures can be used for a database. The three types of recovery models that you can choose from in SQL Server are:

• Simple Recovery Model
  This model is used for databases that do not need to be restored to a specific point in time but can be restored to a point in time when a database backup occurred. Every transaction is written to the transaction log but the log is truncated each time the database reaches a transaction checkpoint, leaving no log entries for disaster recovery purposes. This is the reason why no transaction log backup can be performed on a database with a simple recovery model.

• Full Recovery Model
  This model is the most complete recovery model and is used for databases that need to be recovered to a specific point in time or to the point of failure. SQL Server logs all operations to the transaction log where it is kept until a transaction log backup or truncation occurs. A combination of database backups and transaction logs backup can be used to create a disaster recovery plan. The full recovery model also supports restoring individual data pages while simple recovery model does not.

• Bulk-logged Recovery Model
  This model works like full recovery model except it minimally logs bulk operations to the transaction logs. This model should be used during large bulk operations to increase performance and to avoid the transaction logs from growing too large (aka reduce log space consumption). Bulk-logged model does not support the point-in-time restore option. Since both full recovery and bulk-logged recovery models rely on backing up the transaction log they ensure less data loss than the simple recovery model.

Each of these models represents a different approach to balancing the trade-off   between requirements such as performance, disk space and data loss. Each database can have only one recovery model at any point in time, but each database can use a different recovery model (except the TempDB), so depending on the operations being performed, changing the recovery model for a database may be appropriate.

Finding the Recovery Model

Method 1:

The built-in function called DATABASEPROPERTYEX can be used to retrieve the recovery model for one database or all the databases in an instance.

This script returns the recovery model for one database:

Select DATABASEPROPERTYEX([database_name],'RECOVERY')

E.g. Select DATABASEPROPERTYEX('ADVENTUREWORKS2008', 'RECOVERY')

This script returns the recovery model information for all the databases in an instance:

SELECT name as DB_Name, 

       DATABASEPROPERTYEX(name, 'Recovery') as Recovery_Model

FROM   master.dbo.sysdatabases

Method 2:

Another script that returns the model information for all the databases in an instance:

SELECT name AS [DB_Name],

recovery_model_desc AS [Recovery_Model]

FROM sys.databases

Method 3:

SQL Server Management Studio (SSMS) can be used to find the recovery model for one database.

1. Connect to the instance
2. Right click on the database
3. Choose Properties and go to the options page

Switching the Recovery Model

Method 1: Using SSMS

Follow the steps in Finding the recovery method: Method 3 (above). You can then change the mode from the drop down list as shown below.

Method 2: T-SQL

The basic syntax:

ALTER DATABASE database_name

SET RECOVERY  FULL | SIMPLE | BULK_LOGGED

E.g. ALTER DATABASE AdventureWorks2008 SET RECOVERY SIMPLE

Since recovery models can affect backup and restore procedures it is important to understand the effect of a changing the model. For more information check the following links:

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