Complete and Incremental Backups in Operations Manager
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.
By default, the report server database uses a full recovery model. Other Operations Manager databases use a simple recovery model. For more information about backup options, see Backup Overview (SQL Server).
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 matches the state of the database at the time the backup finished, without 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.
An incremental (differential) database backup records only the data that has changed after 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 finished.
The transaction log is a serial record of all the transactions that have been performed against the database after the transaction log was last backed up. With transaction log backups, you can restore the database to a specific point in time (for example, before 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.
The data warehouse database uses a simple recovery model that truncates all transactions after completion. This means that backing up the log file is insufficient. Perform a complete database file backup.
For more information about recovery models, see Recovery Model Overview.