Small Overview of SQL Server Recovery Models

SQL Server supports three recovery models: simple, full, and bulk-logged. Through these recovery models, you can manage log files as well as prepare your database for best possible recovery. Typically, a database uses the full recovery model or simple recovery model. You can also switch to another recovery model at any time.

Simple Recovery Model

This approach allows the database to be recovered to the most recent backup. If you used databases with simple recovery model that means you don’t want to take log backups, you may restore full or differential backups only. In this recovery model, database restoration to a given point in time is not possible, you may only restore it to the exact time when a full or differential backup occurred. If you are using this recovery model, you will automatically lose any data modifications made between the time of the most recent full/differential backup and the time of the failure.

Full Recovery Model

This approach allows the database to be recovered to the point in time of failure. In this model, SQL Server save the transaction log until you back it up. This model includes a combination of full and differential database backups in conjunction with transaction log backups. In case of database failure, you have the most flexibility restoring databases using the full recovery model. In addition to save data modifications stored in the transaction log, this model allows to restore a database to a specific point in time.

Bulk-logged Recovery Model

The bulk-logged recovery model works same like the full recovery model, the only difference is the handling way to bulk data modification operations. The bulk-logged model records these modification operations in the transaction log using a technical process known as minimal logging. Microsoft recommends that this model only be used for short time.

Conclusion

In this article I just covered the basics of sql database recovery models. After reading this article, You can move to the right direction- which recovery model to be used to prevent data loss. I strongly recommended Full recovery model  to avoid data loss and to achieve Point in Time Recovery.