SQL Server Recovery Models

 

Recovery Models

.

Types of recovery models

All SQL Server database backup, restore, and recovery operations are based on one of three available recovery models:

Simple

Full

Bulk-Logged

Recovery model	Description	Work loss exposure	Recover to point in time?
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. 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 -Always On or Database mirroring -Media recovery without data loss -Point-in-time restores	Changes since the most recent backup are unprotected. In the event of a disaster, those changes must be redone.	Can recover only to the end of a backup. For a more in depth explanation of the Simple recovery model,
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,	Normally none. If the tail of the log is damaged, changes since the most recent log backup must be redone.	Can recover to a specific point in time, assuming that your backups are complete up to that point in time. For information about using log backups to restore to the point of failure. Note: If you have two or more full-recovery-model databases that must be logically consistent, you may have to implement special procedures to make sure the recoverability of these databases.
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. Log backups may be of a significant size because the minimally logged operations are captured in the log backup. For information about database backups under the bulk-logged recovery model.	If the log is damaged or bulk-logged operations occurred since the most recent log backup, changes since that last backup must be redone. Otherwise, no work is lost.	Can recover to the end of any backup. Point-in-time recovery is not supported.

Recovery models and supported restore operations

The restore operations that are available for a database depend on its recovery model. The following table summarizes whether and to what extent each of the recovery models supports a given restore scenario.

RECOVERY MODELS AND SUPPORTED RESTORE OPERATIONS
Restore operation	Full recovery model	Bulk-logged recovery model	Simple recovery model
Data recovery	Complete recovery (if the log is available).	Some data-loss exposure.	Any data since last full or differential backup is lost.
Point-in-time restore	Any time covered by the log backups.	Disallowed if the log backup contains any bulk-logged changes.	Not supported.
File restore *	Full support.	Sometimes.**	Available only for read-only secondary files.
Page restore *	Full support.	Sometimes.**	None.
Piecemeal (filegroup-level) restore *	Full support.	Sometimes.**	Available only for read-only secondary files.