Simple Recovery Model

[Total: 9    Average: 3.6/5]

The simple recovery model allows full and differential database backups only and there is no chance of making transaction log backup. At the time when a checkpoint is created in simple recovery model, all committed transactions are removed from transaction log. That means that point-in-time recovery isn’t possible.

So in which cases is it better to use simple recovery model? There are four of them:

  • There is no need in backing up transaction logs
  • There is no need in point-in-time recovery
  • Losing some data is perfectly fine
  • Database changes are infrequent

Suppose full and differential backups are regularly created:Simple Recovery Model
As seen from the picture above, the full database backup was made at 10:00 and 22:00 and the differential backup was made at 16:00. Assume that some crucial data were deleted at 19:00. The best thing that is possible to do is to restore the database as of 16:00. First you need to restore the full backup (from 10:00) and then followed by the differential backup (from 16:00). In this case, all changes that were made within the time period from 16:00 to 22:00 will be lost.

The database can be restored in the following way:

RESTORE DATABASE your_database FROM DISK = 'full_10_00.bak' WITH NORECOVERY, REPLACE
RESTORE DATABASE your_database FROM DISK = 'diff_16_00.bak' WITH RECOVERY

This is how you can change your database recovery model to Simple:

ALTER DATABASE your_database SET RECOVERY SIMPLE