SQL recovery models

MSSQL database have many levels of backup, each level of the backup has a specific purpose and are used in combination to enable point in time recovery. This post will cover some of these backup levels:

TypeNote
FULL BackupThis is the base of all other levels of backup.
Differential BackupAlso known as ‘Cumulative Backups
Log BackupIncremental backup of data in ‘Transaction logs’
Transaction logsStores change transactions applied to database

Simple Recovery model

ALTER DATABASE db_name SET RECOVERY SIMPLE

The recovery model of the database affects what level of backups can be used for the database. Simple Recovery model allows only Full and Differential backups only and restoring Transaction log backups are not possible. This means no point in time recovery.

Simple recovery model should not be used if a point in time recovery is needed and when data loss up to Full and Differential backups is acceptable.


Restoring sequence : Full backup > Differential backup

source – limit transaction log file size growth
source – Simple recovery mode

Full Recovery model

Full recovery model is used when a point in time recovery is needed. This is because the Full recovery model does not truncate committed transaction until a Full backup is taken. Differential and Log backup does not re-base until the next Full backup. Below shows how to set Full Recovery mode:

ALTER DATABASE db_name SET RECOVERY FULL
Restoring sequence : Full backup > Differential backup > Transaction Log backups

source – Full recovery mode
source – Mssql backup types

Leave a Reply

Your email address will not be published. Required fields are marked *