Recovery models

Microsoft SQL Server databases operate in one of three recovery models: simple, full, or bulk-logged. Most databases use either the simple or full recovery model. It's important to choose the correct recovery model for each of your SQL Server databases.

Simple Recovery Model

The most commonly used SQL Server recovery model, the simple model performs no log backups and requires the least amount of administration. Databases operating in the simple recovery model can be recovered up to the point of the most recent full or differential backup. Therefore, data loss exposure increases over time until the next full or differential backup is taken.

For databases using the simple recovery model, increasing the frequency of backups will reduce the potential for data loss.

Databases operating in the simple recovery model automatically manage transaction log data, so you needn't be concerned with excess log file growth.

Full Recovery Model

Databases operating in the full recovery model have the potential to recover to a specific point in time, offering you the potential to design a more flexible disaster recovery plan that includes full and differential backups in combination with transaction log backups.

Log backups need to be performed regularly on databases operating in the full recovery model in order to prevent log files from becoming too large.

Bulk-Logged Recovery Model

The bulk-logged recovery model behaves similarly to the full recovery model, but doesn't permit point-in-time recovery. Bulk operations performed on such databases will use minimal log space and will benefit from improved performance.

Default Recovery Models

SQL Server Express databases default to the simple recovery model. Most other SQL Server editions default to the full recovery model.

Log Truncation

It isn't necessary to manually truncate SQL Server transaction log files. Truncation frees space within the log file for reuse, and happens automatically:

• Simple recovery model - Truncation occurs automatically at regular database checkpoints.
• Full or bulk-logged recovery models - Truncation occurs automatically after each transaction log backup. This is why regular log backups are essential for these recovery models – without them, the log file will grow continuously.

Note that truncation does not reduce the physical size of the log file. Shrinking the log file should be an uncommon operation performed manually (usually as part of a database migration, bulk import, etc.), and should not be part of any routine maintenance or scheduled backup operation.