SQL Server recovery models

Two types of SQL Server recovery model:

• Full recovery model

• Simple recovery model

Full recovery model

In this model, two types of backups are kept and used during disaster recovery.

• A database backup is scheduled at regular intervals

• Transaction log backups are also done, usually at higher frequency.

• The first transaction log backup performed after a database backup has transactions that have occurred since the database backup.

• The next transaction log backup contains the transactions since the previous transaction log backup.

• When the next database backup is done, the transaction log is truncated and the process begins again.

• The SQL Server Restore Wizard makes it easy to restore a backup and run transactions that occurred since the backup was done.

This recovery model makes it possible to restore a database closer to the time of interruption but requires that the size of the transaction log is kept under control

Simple recovery model

In this model, only database backups are scheduled. This removes the requirement to control the size of the transaction log.

Related topics

• Decide on a SQL Server backup strategy

• Rebuilding SQL Server indexes