Every version of MS SQL provides 3 different recovery models that define the logging required in case of disaster recovery.
- Full Recovery Model
- Simple Recovery Model
- Bulk-logged Recovery
Lets begin with the more detailed recovery model for MS SQL, Full Recovery. This model is for the recovery of data up to a certain point in time. Obviously, this option will create very large transaction log files, which gives you more flexibility during the restoration. The server will keep the transaction log until a backup is performed.
The second MS SQL recovery model, Simple Recovery, keeps the strict minimal amount of information as it will be the server that will decide to truncate the data when it reaches a transaction checkpoint. As soon as that is reached, there is no turning back for a chance to recover data.
If full or differential backups were performed, the possibility to restore is available from those backups, but not with a specific time stamp checkpoint, introducing inconsistencies in your data.
Lastly, the Bulk-logged recovery model for MS SQL is a “special-purpose” model that works in a similar manner to the full recovery model. The only difference is in the way it handles bulk data modification operations. The bulk-logged model records these operations in the transaction log using a technical term known as “minimal logging”. This saves on processing time, but prevents you from using the point-in-time restore option offered in the Full Recovery.
Microsoft recommends that the bulk-logged recovery model only be used for short periods of time. Best practice dictates that you switch a database to the bulk-logged recovery model immediately before conducting bulk operations and restore it to the full recovery model when those operations complete.
We’ve included two YouTube videos below that should help provide a visual guide to the MS SQL Recovery Model Overview as well as serve as a tutorial on how to set the recovery model on MS SQL server 2012
Leave a Comment