Full Recovery Model

The Full Recovery Model is the most resistant to data loss of all the recovery models. The Full Recovery Model makes full use of the transaction log – all database operations are written to the transaction log. This includes all DML statements, but also whenever BCP or bulk insert is used.

For heavy OLTP databases, there is overhead associated with logging all of the transactions, and the transaction log must be continually backed up to prevent it from getting too large.

Benefits:

Most resistant to data loss
Most flexible recovery options – including point in time recovery

Disadvantages:

Can take up a lot of disk space
Requires database administrator time and patience to be used properly

Bulk-Logged Recovery Model

The Bulk-Logged Recovery Model differs from the Full Recovery Model in that rows that are inserted during bulk operations aren’t logged – yet a full restore is still possible because the extents that have been changed are tracked.

Benefits:

Transaction log stays small
Easier from an administration standpoint (don’t have to worry about transaction logs)

Disadvantages:

Not for production systems
Point in time recovery not possible
Least data resistant recovery model

Simple Recovery Model

The simple recovery model is the most open to data loss. The transaction log can’t be backed up and is automatically truncated at checkpoints. This potential loss of data is makes the simple recovery model a poor choice for production databases. This option can take up less disk space since the transaction log is constantly truncated.

Benefits:

Transaction log stays small
Easier from an administration standpoint (don’t have to worry about transaction logs)

Disadvantages:

Not for production systems
Point in time recovery not possible
Least data resistant recovery model

Advertisements