Database Backups

When creating database backups, but no separate transaction log backups, the only recovery procedure necessary is to restore the last backup of the database. This re-creates the database as it was when the backup operation completed. However, there is no way to recover any modifications made to the database after the most recent database backup completed.

Some of the characteristics of a system for which you would consider using database backups only include:

• The importance of the data is low enough that losing any modifications made after the last backup can be tolerated because it is more efficient to endure the loss and possibly re-create the data manually rather than use transaction log backups. For example, a test database used in the development of a database application can tolerate lost data because the data is not vital and can be re-created easily.
• You can re-create the data, for example, from batch loads that take place nightly and replace the contents of most of the database.
• You need to implement simple maintenance procedures, for example, because you do not have a database administrator.

  Maintenance procedures can be simplified further using the Database Maintenance Plan Wizard.

• Database changes are infrequent, such as a read-only database.

Backing up a database backs up all the data in the database regardless of whether it changed after the last database backup was created. This means that the entire database backup is self-contained and does not rely on any other backup medium to be restored. It also means that the database backup will use more storage space per backup, and consequently need more time to complete the backup operation, compared to using transaction logs and differential database backups.

Note Simple database backups are not an appropriate choice for production systems where loss of recent changes is unacceptable.

Important When creating database backups only, it is recommended that the transaction log is set to be truncated automatically every time a checkpoint occurs in the database by setting the trunc. log on chkpt. database option to TRUE. This helps prevent that transaction log from becoming full, requiring the transaction log to be truncated manually.

See Also