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:
|FULL Backup||This is the base of all other levels of backup.|
|Differential Backup||Also known as ‘Cumulative Backups‘|
|Log Backup||Incremental backup of data in ‘Transaction logs’|
|Transaction logs||Stores 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.
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