Microsoft SQL Server Database Basics
Appendix C: Backup and Recovery Best Practices 113
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 CA
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 2005 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
2005 or later)
Optional
Optional
Optional
Backup Transaction
Log After Database
Optional
Optional
