The recovery model determines how database transactions are logged and what degree of concern is provided to data loss in case of any failure. In SQL Server 2005, three recovery models are available.
- Full Recovery Model of SQL Server 2005
The full recovery model does the most extensive logging and allows the database to be recovered to the point of failure. Full recovery model presents the highest protection against data loss. You should always configure all production databases to use full recovery.
- Bulk-logged Recovery Model of SQL Server 2005
The bulk-logged recovery model fully logs transactions but only minimally logs most bulk operations, such as bulk loads, SLECT INTO, and index creations. Bulk-logged recovery model allows the database to be recovered to the end of a transaction log backup only when the log backup contains bulk changes. Recovery to the point of failure is not supported.
- Simple Recovery Model of SQL Server 2005
The simple recovery model minimally logs most transactions, logging only the information required to ensure database consistency after a system crash or after restoring a database backup. With simple recovery model the database can be recovered only to the most recent backup. This recovery model has the maximum exposure to data loss and should not be used where data loss in the event of a crash cannot be tolerated.