Recovery Model Behavior

Full Recovery Model – This recovery is the most common recovery model used for SQL Server. This model is required for database mirroring and availability groups.

  • Allows log backups and zero-to-minimal data loss after a disaster
  • All changes to the database are fully logged to the transaction log file
  • The transaction log will not clear data until a log backup has been performed, meaning that the log backup is what clears the transaction log
    • This means that log backups are required, otherwise the transaction log will continually grow until your drive is full

Simple Recovery Model – Commonly used when log backups and point-in-time recovery are not required.

  • Some operations can use minimal logging
    • All other operations are fully logged, as in the FULL recovery model
  • The transaction log will not clear data until a log backup has been performed, meaning that the log backup is what clears the transaction log
  • Log backups are NOT possible with this recovery model

Important -Disaster recovery is only possible using full and differential backups, so data loss will occur back to the most recent data backup. Recovery is not an appropriate choice for production systems where loss of recent changes is unacceptable.

Bulk_Logged Recovery Model – Commonly used to minimize log growth during bulk operations, while preserving the ability to take log backups.

  • Some operations can use minimal logging
    • All other operations are fully logged, as in the FULL recovery model
  • The transaction log will not clear data until a log backup has been performed, meaning that the log backup is what clears the transaction log
    • This means that log backups are required, otherwise the transaction log will continually grow until your drive is full
  • The time running in this mode should be minimized to reduce potential loss of data

Important – As mentioned above, the Bulk_Logged recovery model should not be used if there is the possibility of losing user transactions.

 

 

One thought on “Recovery Model Behavior

  1. Pingback: Maintenance Tasks | sqldatabasics

Leave a Reply

Your email address will not be published. Required fields are marked *