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 -- Allowsyour 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 2012 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 -- Allowsyour 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 2012 or later) |
Not Available |
Optional |
Optional |
|
Partial Database (Requires SQL Server 2012 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.