Recovery models serve very important roles when it comes to MS SQL Server backup. The purpose of an MS SQL Recovery Model is to control how transactions are logged, whether it allows and requires backing up and the types of restore operations that are available. By controlling transaction log maintenance, databases are able to be converted from one recovery model to another at any time. The three types of recovery models are simple, full, and bulk-logged, with most databases using either the simple recovery model or full recovery model. Whichever model is used, there are differences between the three.
Simple Recovery Model
The most widely-used MS SQL Recovery model, the simple model requires no log backups and can recover only to the end of a backup. While this is convenient, a drawback with this model is that in the event of a disaster, the work loss exposure tends to be more than with other models. All changes since the most recent backup are unprotected, meaning they will have to be redone.
Because of its limited capabilities regarding backups, there are some features that are not able to be used in simple recovery mode. These can include:
- Log shipping
- Point-in-time restores
- Media recovery without data loss
- AlwaysOn or Database mirroring
Full Recovery Model
Full recovery models differ from simple recovery models in that they can recover to a specific point in time, letting backups be more complete. Normally no work is lost, but if the log’s tail is damaged, changes since the most recent log backup will need to be redone.
The major points when it comes to full recovery models are:
- Recovering to a point in time, usually prior to application or user error
- Lost or damaged data files will not lose any data
Bulk-logged Recovery Model
This type of MS SQL Recovery model does not support point-in-time recovery but can recover to the end of any backup, making it invaluable in the event files are damaged. Most of the time, no work is lost with this model, but if bulk-logged operations happened prior to the most recent log backup, any changes made since that last backup will need to be redone.
The major features of this model include:
- Permitting high-performance bulk copy operations
- Reducing log space by minimizing logging during bulk operations