SQL Database Recovery Model

Before planning on database backups, it is important to understand the recovery model that need to be defined for the database. Recovery model determine how transactions are being logged and the type of database restore operations are available.

The three recovery models are simple, full and bulk logged.

Simple Recovery Model – Simplest form of recovery model. Support full and differential backup. No transactional log backup. Transactional log space is automatically reclaimed. Potential work loss between most recent backup and event of disaster, as it can only recover data back to the most recent backup. This model could be used for database that the point of failure recovery is unnecessary (loss of recent data changes is acceptable) and do not need to backup and restore transactional logs.

Full Recovery Model – Support full, differential and transactional log backup. Log backup is required. This model with proper log backup can recover to point in time (or to a mark). Work loss is minimal or no work loss at all if tail of log is not damaged and could be back up. Ideal for production system require no work loss and need to be able to recover to point of failure, as well as the support for database with multiple filegroup, and option for piecemeal restore of read-write secondary filegroup and individual pages.

Bulk Logged Recovery Model – Support full, differential and transactional log backup. Log backup is required. This recovery model use minimal logging for most bulk operations in order to reduce log space. Since the bulk operations are minimally logged, it permits higher performance. However, since not all transaction are logged, it can only restore data to last backup but not point in time recovery. This model is an adjunct to full recovery model that often only intermittently used to improve bulk operations performance. The recovery model can switch from full recovery model to bulk logged recovery model right before the bulk operations, and immediately switch back to full recovery model after the operation. It is recommended that a log backup is taken right before and after switching to bulk logged recovery model to achieve minimal or no data loss. Please be aware that restoring transaction logged under bulk logged recovery model may be exposed to data loss.

To change database recovery model to simple recovery model,

USE master;
ALTER DATABASE YourDatabase SET RECOVERY SIMPLE;

To change database recovery model to full recovery model,

USE master;
ALTER DATABASE YourDatabase SET RECOVERY FULL;

To change database recovery model to bulk logged recovery model,

USE master;
ALTER DATABASE YourDatabase SET RECOVERY BULK_LOGGED;

To verify the database recovery model, examine its recovery_model and recovery_model_desc in sys.databases catalog view.

USE master;
SELECT recovery_model, recovery_model_desc
FROM sys.databases
WHERE database_id = DB_ID(N'YourDatabase');