Complete and Incremental Backups in Operations Manager 2007

Applies To: Operations Manager 2007 R2, Operations Manager 2007 SP1

You must ensure that database backups are as recent and complete as possible. This topic provides information to help you decide how to incorporate both complete and incremental database backups into an overall backup plan.

Complete Database Backups

A complete database backup captures the entire database, including all entries in the transaction log, and excluding any unallocated extents in the files. Pages are read directly from disk to increase the speed of the operation.

You can re-create a database from its backup in one step by restoring a backup of the database. The restore process overwrites the existing database or creates the database if it does not exist. The restored database will match the state of the database at the time the backup completed, minus any uncommitted transactions. Uncommitted transactions are rolled back when the database is restored.

A complete database backup uses more storage space per backup than transaction log and incremental database backups. Consequently, complete database backups take longer and therefore are typically created less frequently than incremental database or transaction log backups.

Incremental Database Backups

An incremental (differential) database backup records only the data that has changed since the last database backup. You can frequently make incremental backups of a database because incremental database backups are smaller and faster than complete database backups. Making frequent incremental backups decreases your risk of losing data.

In case of database failure, you can use incremental database backups to restore the database to the point at which the incremental database backup was completed.

Transaction Log Backups

The transaction log is a serial record of all the transactions that have been performed against the database since the transaction log was last backed up. With transaction log backups, you can restore the database to a specific point in time (for example, prior to entering unwanted data) or to the point of failure.

When restoring a transaction log backup, Microsoft SQL Server rolls forward all changes recorded in the transaction log. When SQL Server reaches the end of the transaction log, the state of the database is exactly as it was at the time the backup operation started. If the database is recovered, SQL Server then rolls back all transactions that were incomplete when the backup operation started.