SQL Server Database Recovery Model Part 1

Ever wondered what you’d be facing if one of your Crucial Databases was accidentally dropped off??? Trust me; it would be a disastrous situation!
But the situation would have been deal able if a little bit of attention was taken before hand on selecting the apt recovery model for your database.

So what is the Recovery Model?
A Recovery model is a database property that controls how the transaction log is managed. The model determines how much data loss is acceptable in case of a failure and what types of backup and restore functions are allowed.

Let’s explore the types of Recovery models available.
There are three of them namely: Full Recovery, Bulk-Logged Recovery and the Simple Recovery Model.
The main difference between all the three models is:
How the transaction log backup is maintained and If there’s a possibility of data loss.

Coming to the basics of each of Model:

The Full recovery Model
This model creates a complete backup of the database as well as an incremental back up of all the changes that have occurred since the last full back up. The best thing about this model is that it’s possible to recover the database to a particular point in time. Selecting this mode would require you to have enough space available for the transaction log to store all the transactions that occur between each backup because the space in the transaction log is only reclaimed when a backup of the transaction log is made.

The Bulk-Logged recovery Model
Bulk-logged recovery is quiet similar to the Full Recovery model but the Bulk copy operations are only minimally logged in this recovery Model thereby giving you better performance, i.e. the SQL Server only logs the minimum necessary to recover the data if a backup is needed and due to this sole reason if a bulk copy operation occurs, point-in-time recovery is not possible.

The Simple recovery Model
The simple model as the name suggests, is the easiest one to manage. It allows only full back ups and the drawback in this case is that there’s no way you can back up only the changes made since the last back up. But considering the fact that the transaction log would hardly become full because of the transactions occurring between the full backups, this model would be beneficial. When using this Recovery Model, the space in the log is reclaimed whenever the database performs a checkpoint.