During the past three years, I've often been amazed at just how difficult it can be to get IBM's DB2 to successfully restore from its own backups. Even in the most simple case ( offline backups, with circular logging) you can run into difficulty, but it can be incredibly difficult if the database has been configured to use log shipping, and especially if the backups were taken "online" - that, it seems, can be most the difficult DB2 backup / restore scenario. And, of course, DB's error messages usually offer very little (to the untrained eye) to explain what's going wrong.
However, over time, I have finally figured out most of the problems, and so most restorations work quite smoothly for me now. So, I thought I'd quickly share the most useful (for me) backup and restore commands.
First off, to perform a full, online backup of a DB2 database that has been configured for log shipping (really, the only viable option for any important, live database that needs to run 24x7), I do this:
db2 'BACKUP DATABASE <db_name> ONLINE TO <backup_dir> INCLUDE LOGS WITHOUT PROMPTING'
Of course, that's the easy part. It will do the following:
- close the current active log file.
- take an in-time snapshot of the database, while writing any new activity to new active log files.
- when the snapshot is complete, it will include the newly-created active logs files (if any) in the backup file also.
Now the part that can cause a lot of grief, is the restoration. And basically, most of the problems I have experienced, are related to the handling of those included log files. All you need to do is instruct DB2 to use them, and things should go well for you. However, it can be difficult to realise just how to do that at first, and I said earlier, DB2's error messages do not offer much help.
So, without any further rambling, here's how to restore a DB2 database from the above backup:
db2 'RESTORE DATABASE <db_name> FROM <backup_dir> LOGTARGET <tmp_log_dir>'
db2 'ROLLFORWARD DATABASE <db_name> TO END OF LOGS AND COMPLETE OVERFLOW LOG PATH ( <tmp_log_dir> )'
So again, it's pretty simple once you've got it right. The first command will restore the database to the state of the
in-time snapshot, and extract (but not use) the included post-snapshot log files to the tmp_log_dir directory.
Then, the second command will cause DB2 to process the log files from the tmp_log_dir (which were extracted to there
in the first command).
Also, if you happen to have more log files (which you normally would), you can either add them the to the tmp_log_dir
too, or add their own path inside the round parentheses in the second command.
Well, that's it (sort of, I mean, obviously there are many, many more options for all three commands)... so hopefully that will help someone out there. It would have helped me incredibly, over the past few years, had someone already posted this information somewhere I could find it!! :)