Search This Blog

SQL Server Recovery Model overview

Simple
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
  • 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?
Following features cannot be used in full recovery mode:
  • Log shipping
  • AlwaysOn or Database mirroring
  • Media recovery without data loss
  • Point-in-time restores
Bulk-logged
Requires log backups.
An adjunct of the full recovery model that permits high-performance bulk data operation. Reduces log space usage by using minimal logging for most bulk operations.
Following features cannot be used in bulk-logged recovery mode:

  • Log shipping
  • Media recovery without data loss