SQL Server Recovery Models Explained

SQL Server recovery models often incorrectly referred to as (MS SQL Recovery Mode) define how your SQL Server database will treat your transaction logs.

There are 3 SQL Server Recovery Models to choose from, Simple, Full and Bulk Logged.

Simple: The transaction log is written to as a transaction runs and once the transaction completes what is in the transaction log file from that transaction is release to be reused again.

Full: The transaction log is written to as a transaction runs, and that space is not released until the transaction log is backed up.

Bulk-logged: Similar to the full transaction logging in that the space is not released until the transaction log is backed up. The difference is that with the Bulk Logged recovery model, certain transactions that can be minimally logged will be smaller in the transaction log file than if you were using the full recovery model. This is typically bulk copy operations that are minimally logged.

So what should I be using? Most of the time the answer is going to be Simple or Full. If you are okay with doing full backups daily (or on any schedule) and only being able to recovery to the point in time of the full backup, then Simple Recovery Model will be fine. If you need a more granular approach where if you need to recovery from backup you would want to recover to a specific point in time, then Full (or Bulk-Logged) would be your choice for the recover model. If you are going to be using the Full recovery model (or Bulk-Logged) you will want to be running regular transaction log backups in addition to your full backups.

Recovery Model and Transaction Log Related Posts:

• Visualizing VLF’s
• Switching Multiple Databases to Full Recovery Model
• SQL Server out of Disk Space
• DBCC Shrink file
• Why is my log file not shrinking?

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!