Recovery models for SQL Server
For Microsoft SQL Server, the recovery model is a database setting, changed either through Management Studio or the ALTER DATABASE statement. There are three recovery models for SQL Server: full, bulk-logged, and simple. Each is summarized here.
Full recovery model
Full recovery allows point-in-time (or point-of-failure) recovery, provided you have the transaction log backups at that point in time. Point-in-time recovery signifies the ability to recover a database to a specific time, restoring all committed transactions and rolling back all incomplete transactions.
Recovery to a point of failure or point in time involves the following:
- Backing up the currently active transaction log
- Restoring the most recent full database backup without recovery
- Restoring the most recent differential database backup without recovery
- Restoring in sequence any transaction log backups without recovery
- Restoring the last transaction log backup with recovery
You would use the full recovery model for a production database that contains critical data.
Bulk-logged recovery model
Databases using the bulk-logged recovery model minimally log bulk operations to the Microsoft SQL Server transaction log. These operations include CREATE INDEX, SELECT … INTO, writetext, updatetext, and BULK INSERT. The transaction log does not record sufficient information to recover these changes if media failure occurs after a bulk operation. You can recover a database to the point of failure, but your data might not be consistent if it was changed by a bulk operation. The process of restoration is the same as that of full database recovery.
Sites that perform bulk operations on a regular basis when other users are not accessing the database might want to use the bulk-logged recovery model. Or you might use the full recovery model most of the time but switch to the bulk-logged recovery when doing bulk operations to save disk space that would be used by the transaction log when doing full database recovery and improving database performance during the bulk operation.
Simple recovery model
The simple recovery model does not use the transaction log for recovery. If you use the simple recovery model, you can only restore full database backups. Therefore, data is only recoverable to the most recent backup and point-in-time recovery is not possible. Changes made since the last database backup are lost.
The advantage of using the simple recovery model is that there is less transaction log and log space management necessary. The simple recovery model can be used for development databases or databases that are not edited that frequently.