Db2 – How to retain backups if automatic backup is enabled

backupdb2db2-luw

Is there a way to retain the backups or configure the interval when they are deleted? According the manuals it's not possible:

"If backup to disk is selected, the automatic backup feature will
regularly delete backup images from the directory specified in the
automatic database backup configuration. Only the most recent backup
image will be available at any given time, regardless of the number of
full backups that are specified in the automatic backup policy file."

It seems strange to me that it's not possible to retain backups.

Best Answer

Generally it is desirable to have old backups pruned at some point otherwise your backup file system is liable to run out of space at some point causing further backups to fail (this assumes that more recent backups are more valuable than aged backups, which is generally the case).

In DB2, there are several database-level configuration parameters which dictate when automatic backups are retained or removed which include:

  • logarchmeth1 - the primary method for archiving logs
  • auto_del_rec_obj - automatically remove log files, backup images, etc. when the associated recovery history entry is pruned
  • rec_his_retentn - the number of days that history entries are retained for
  • num_db_backups - the number of full database backups to retain for a given database

A typical setup might be:

  • logarchmeth1 set to DISK:somepath to ensure that transaction logs are saved somewhere for recovery
  • auto_del_rec_obj set to ON to ensure that old backup objects (log files and full backups) are removed when their associated history entries are purged
  • rec_his_retentn set to 30 to prune history entries after 30 days
  • num_db_backups set to 4 so that up to 4 full database backups are available at any given time

You might then set up a weekly cronjob to perform a full online backup of the database (presumably during some reasonably low-usage hours; a more nuanced strategy might involve incremental backups). As you'd be performing weekly backups you'd take about 4 full backups per month which pairs nicely with the 30 days of history pruning.

If something went horribly wrong and you needed to restore, you'd do so presumably from the latest weekly backup then roll forward through the retained logs to bring your database back to the point you wanted. And in case something was wrong with that backup you've got three more full backups before it to try as well.

Your backup file-system should largely look after itself with reasonable settings; just keep an eye on the amount of used storage (as your database grows over time so will your full backups, and if you experience a growth in transaction level, the average amount of log storage may go up too) and expand it as necessary to keep it within sensible limits.

There are various caveats to be aware of. Read up on the effects of the PRUNE HISTORY and ARCHIVE LOG commands particularly, in addition to the obvious BACKUP DATABASE and RESTORE DATABASE.

Related Question