The recovery models of SQL Server define how different backup and restore tasks will be performed on your database. When choosing a recovery model, you need to identify your business requirements, i.e. log space consumption, restore options for your database, simplicity of disaster recovery planning, and more. In general, log maintenance and backup is the key concept that differentiates one model from the other two. Let's take a peek into each of these recovery models.

Simple Recovery Model

As the name implies, this model is indeed that simple. In the simple recovery model, SQL Server automatically performs transaction log truncation when a transaction checkpoint is reached. This is done to reclaim the log space, and thus keeping the log space consumption to minimum.

This recovery model does not allow for transaction log backups. You can only perform full or differential backups of the database. Each transaction is recorded in the transaction log. When the transaction is finished and data is written to the SQL database, the log is truncated and the space used by this transaction can be reused by new transactions. As there is no information in the transaction log, you cannot restore your database to a specific point in time. You can only restore it to a time when you took a full or differential database backup. Thus, the changes made by you since the last full or differential backup cannot be restored.

When you should end up going with Simple Recovery Model:

• It is easy to recreate the data or redo the changes since the last backup.
• Loss of transactions since the last backup is acceptable.
• Your data is not subject to change frequently.

Full Recovery Model

In a full recovery model, the transactions within the transaction log are kept unless the transaction log is backed up or truncated. Here, you can carry out full or differential database backups along with transaction log backups. Since you keep track of all the transactions that occur to the database, you can easily do a point in time recovery. In case an error was reported and the transaction could not complete, you can roll back your database to a point before this transaction.

A database is set to full recovery model when the data inside the database is mission-critical or when you are using database mirroring.

In order to set your database to 'Full Recovery' using SQL Server Management Studio, follow these steps:

• Right-click your database and then, click 'Properties'.
• Navigate to the 'Options' page.
• Choose 'Full' for recovery model and click 'OK' to save these settings.

Bulk-Logged Recovery Model

In this recovery model, SQL Server uses a technique called 'minimal logging' to record bulk data modification operations (such as BULK INSERT, CREATE INDEX, SELECT INTO) in the transaction log. This technique helps to save log space and minimize processing time. It helps to keep your transaction logs from getting oversized due to bulk operations. You still have an option to restore your database to a point in time until a bulk operation is recorded in the log.

The Bulk-logged recovery model is the best fit for situations that require you to perform bulk operations separately from normal processing. You can switch to this model before performing any bulk operations and set your database to 'Full recovery' after these operations are completed.  

Summary

Undoubtedly, Simple Recovery has the benefit of easy administration. But when it comes to data protection, Full Recovery or Bulk-Logged Recovery, or a combination of two is the perfect match. With that said, each of these recovery models is apt in its own right. You need to consider your business requirements along with the benefits and implications of these models before making the right choice.