1

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.

Improve this question
1

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.

Improve this answer
  • Understood. My setup is rec_hist_retent = 7 and num_db_backups = 4. Automatic backup is enabled. And in my backup directory I see only 2 backup images keeping rotating. Never more than 2.Hence my confusion. – sahhai Dec 8 '16 at 20:15
  • 1
    If rec_his_retentn is 7 it'll be pruning anything (including backups) older than a week so if the extra backups you're expecting to see are older than that, that's probably why they're disappearing (if I recall correctly, num_db_backups is subordinate to rec_his_retentn - you have to set both with a view to how often you perform backups). But it's been quite a while since I looked into this in depth, so it's worth diving into the linked docs and reading through those as I'm probably out of date! – Dave Jones Dec 8 '16 at 21:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.