SQL Server Recovery Models

Recovery Models

The term Recovery Model refers to the three methods in which SQL server database can be configured to facilitate proper backup. Each model has its own advantages and disadvantages.

1. Full Recovery Model

To restore SQL database to a specific point in past, we need to restore the database along with its transaction log. SQL server transaction log will contain records of uncommitted transactions at any time, therefore restoring a SQL database without its Transaction log will not help to restore exact state of a database at a point in past.

When we configure a database with Full Recovery Model, SQL server will not delete transaction log of committed transactions until it is backed up. When a Transaction log of a database configured with Full Recovery Model is backed up, SQL server will delete transaction logs of committed transactions. So, to configure Transaction log backup, the database should be configured With Full Recovery Model.

If a database is configured with Full Recovery model and its transaction log is not backed up very frequently, the transaction log will grow to unlimited size resulting in a server crash!

In Full Recovery model, SQL server will log every kind of transactions including index creation, bulk insert. If a database is used for mission critical applications, we have to configure it with Full Recovery model. Since Index is logged, we don't have to recreate index after transaction log restore.

When we are restoring transaction logs from backup, we have the option to restore the transaction log up to a point at which transaction is committed.

2. Bulk Recovery Model

Bulk Recovery Model is almost similar to Full Recovery model. In Bulk Recovery model bulk operations like bulk insert are not logged(actually minimally logged, there will be a record of transaction but not complete log of it).

Since bulk transactions are not logged, size of transaction log will be considerably less compared to the Full Recovery model. Bulk Recovery model is useful if we have space constraints.

In Bulk recovery model also, frequent transaction log backup is a must since transaction log of committed transactions are not deleted until it is backed up.

3. Simple Recovery Model

Simple Recovery model is the preferable model in most cases. In Simple Recovery Model transaction logs of committed transactions are deleted on SQL server checkpoints. SQL server checkpoint represents a SQL server operation which writes committed transactions to the disk.

In Simple Recovery model , we can not create a Transaction Log backup since transaction log is truncated frequently at checkpoints.

Advantages of Simple Recovery Model

1. It makes SQL server management easy since we don't have to worry about growing size of transaction log.

2. It makes database backup management easier