The recovery model is a property of a Microsoft SQL Server database. It controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged.

The recovery model is a property of a Microsoft SQL Server database. It controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available.

Three recovery models exist:

Simple

No log backups. Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space. For information about database backups under the simple recovery model, see Full Database Backups (SQL Server). Operations that require transaction log backups are not supported by the simple recovery model. The following features cannot be used in simple recovery mode: Log shipping AlwaysOn or Database mirroring Media recovery without data loss Point-in-time restores

Full

Requires log backups. No work is lost due to a lost or damaged data file. Can recover to an arbitrary point in time (for example, prior to application or user error). For information about database backups under the full recovery model, see Full Database Backups (SQL Server) and Complete Database Restores (Full Recovery Model).

Bulk-logged.

Requires log backups. An adjunct of the full recovery model that permits high-performance bulk copy operations. Reduces log space usage by using minimal logging for most bulk operations. For information about operations that can be minimally logged, see The Transaction Log (SQL Server). For information about database backups under the bulk-logged recovery model, see Full Database Backups (SQL Server) and Complete Database Restores (Full Recovery Model).

A database can be switched to another recovery model at any time.

history | excerpt history