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 a simple recovery model, all committed transactions are removed from the transaction log. That means that point-in-time recovery isn’t possible.
So in which cases is it better to use a simple recovery model? There are four of them:
- There is no need to back up transaction logs
- There is no need for point-in-time recovery
- Losing some data is perfectly fine
- Database changes are infrequent
Suppose full and differential backups are regularly created:
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
