Choosing a Backup and Restore Strategy

For each database, you must choose one overall backup and restore strategy: backing up the database only, or backing up the database and the transaction log. Additionally, each backup strategy can be augmented by the use of differential database backups to increase the speed of the restore process.

Choosing whether to back up the database only, or the database and the transaction log, is an important decision because:

• If you choose to back up the database only, you will lose all committed transactions that have occurred after your most recent database backup.

  The primary advantage of using only database backups is simplicity. Backing up is a single operation, usually scheduled at regular intervals. Should a restore be necessary, it can be accomplished easily in one step.

• If you choose to back up the database and the transaction log, you will be able to recover all committed transactions up to the point of failure; only uncommitted work will be lost, unless the active transaction log is also lost, for example due to hardware failure on the disk containing the transaction log files. To prevent loss of the active transaction log, place the transaction log files on mirrored disks.

  Particularly for production databases, it is important not to lose work in the event of a failure. Transaction log backups provide the information necessary to redo changes made after a database backup was performed. Transaction log backups also make possible optimizations and features important to larger, production databases, such as reducing the time to back up and recover the database and allowing the database to be returned to its state at an earlier point in time. Transaction log backups are usually scheduled more frequently than database backups.