Backup and recovery concepts Recovery factors
Backup strategy for read-only and read-write filegroups (continued)
View the read-only backup set.
Do this if needed to confirm all read-only groups have been backed up.
“Viewing read-only backup sets” on page 71
This section is provided to assist you in defining a recovery plan which is suitable to your application environment. Much of this information is based on Microsoft’s SQL Server Books Online. Refer to that resource for a more inclusive discussion.
SQL Server maintains a write-ahead transaction log for each database. This log helps to maintain database updates in cache memory to ensure that data is not written to disk before it has been committed. Database writes occur as a part of the checkpoint procedure. Checkpoint frequency is determined by SQL Server based upon the “recovery interval” which is a configuration parameter indicating the maximum time interval that can be tolerated during a system restart. When checkpoint occurs the portion of the transaction log that is no longer needed for system restart becomes inactive and is optionally truncated, depending upon the recovery strategy in place, as described in the next section.
If the transaction log is not truncated by the checkpoint procedure, then it can be backed up and used for point-in-time recovery, failure from disk crash, or move and copy operations.
SQL Server 2000 and 2005 provide three basic levels for database recovery which have different implications for both backup performance and for the granularity of recovery. These levels are:
With this method the inactive portion of the transaction log is not
retained beyond the database checkpoint, this method provides for minimal usage of log space. However, the database can only be restored to the last full backup. Transaction log restores, including point in time recovery and named transaction recovery are not supported. In addition, maximum performance is provided for bulk operations, such as (Create Index, Select Into, and Bulk Copy) because they are not logged.