Understanding database recovery models

Understanding database recovery models

There are 3 recovery models in Sql Database, these models determines how precisely a database may be restored.

 

1. Simple
2. Full
3. Bulk-logged

Simple model

The simple model does not allow for transaction log backups. As a result, you cannot restore a database to a point in time.Your database is vulnerable to data loss when using this model.That said, using this model does ease the task of administration because SQL Server will reclaim space automati­cally from the transaction log.

Full model

With the full model, data loss is minimal when the transaction log is backed up on a regular basis.Every transaction is fully logged to the transaction log, and the transaction log will continue to grow until it is backed up.While this model does add administrative overhead, your data is protected from data loss.

Bulk-logged model

When you use the bulk-logged model, bulk operations are minimally logged, which reduces the size of the transaction log. Note that this does not eliminate the need to back up the transaction log. Unlike in the full recovery model, in the bulk-logged model you can restore only to the end of any backup; you cannot restore to some point in time

Thanks & Regards  
Srikanth Goud-  Virtualization Administrator