Recovery Models

Backup and Recovery Best Practices › Microsoft SQL Server Database Basics › Recovery Models

Recovery Models

The SQL Server recovery model is a decision that manages the risk of lost information in case of a disaster by controlling the level of transaction log involvement. You may change recovery models on a per-database basis to help you manage database maintenance tasks. Depending on the version of Microsoft SQL Server and the recovery model inherited by a database, certain Arcserve Backup backup options may not be available.

In a given SQL Server instance, your databases can have a mix of the following recovery models:

Simple -- Allows your database to be recovered only to the time of a backup. Transaction log backups are not permitted so any work done after the most recent backup must be redone. File and FileGroup backups are also not permitted, though in SQL 2000 and later, partial database backups are still permitted. The risk of lost information exists and is limited to all changes made since the last backup.
Full -- Allows your database to be recovered to the point of failure, or any point in time. Including transaction log backups is required so you can recover up to a specific point in time. File and FileGroup backups or database differential backups can be optionally included. This model has the lowest risk of data loss and the greatest flexibility during recovery.
Bulk-logged -- Allows high-performance batch operations. Transaction log backups are required but you can recover only until the time of backup. You should perform transaction log backups to truncate the transaction log regularly. File and FileGroup backups or database differential backups can be optionally included.

Backup Method	Simple	Full	Bulk-Logged
Database Full	Required	Required	Required
Database Differential (not available for master db)	Optional	Optional	Optional
Transaction Log	Not Available	Required	Required
File and FileGroup (requires SQL Server 2000 or later)	Not Available	Optional	Optional
Partial Database (Requires SQL Server 2000 or later)	Optional	Optional	Optional
Backup Transaction Log After Database		Optional	Optional

For more information about File and FileGroup Backups, see Files and FileGroup Backups. For more information about Partial Database backups, see Partial Backups.