Standard backup and restore procedures come in three recovery models:
- Simple—The simple recovery model allows high-performance bulk copy operations, minimizes the disk space required, and requires the least administration. The simple recovery model does not provide transaction log backups, so you can only recover data to the point of the most recent full or differential backup. The default recovery model is simple, but is not appropriate in cases where the loss of recent changes is not acceptable.
- Full—The full recovery model has no work-loss exposure, limits log loss to changes since the most recent log backup, and provides recovery to an arbitrary time point. However, the full recovery model uses much more disk space.
- Bulk-logged—The bulk-logged recovery model provides higher performance and minimizes the log space used by disk-intensive operations, such as create index or bulk copy. With the bulk-logged recovery model, you can only recover data to the point of the most recent full or differential backup. However, because most databases undergo periods of bulk loading or index creation, you can switch between bulk-logged and full recovery models to minimize the disk space used to log bulk operations.
When a database is created, it has the same recovery model as the model database. Although the simple recovery model is the default, the full and bulk-logged recovery models provide the greatest protection for data, and the full recovery model provides the most flexibility for recovering databases to an earlier point in time. To change the recovery model for a database, use the ALTER DATABASE statement with a RECOVERY clause.
Regardless of the recovery model you choose, you should keep in mind that backup, restore, and archive operations involve heavy disk
I/O activity. You should schedule these operations to take place in off-peak hours. If you use the simple recovery model, you should set the backup schedule long enough to prevent backup operations from affecting production work, but short enough to prevent the loss of significant amounts of data.
Doc Feedback