Ok, so there are several good reasons for Backing up your Commerce DB2 database
- After Commerce Instance Creation
- As required by your overall ongoing backup and recovery strategy
- Before a FixPack(DB2 or Commerce) or Feature Pack(Commerce)
- After a FixPack(DB2 or Commerce) or Feature Pack(Commerce)
- Before any task that requires change of a lot of data – such as first time pruning, explicit executions of delete statements or update/insert statements that will affect more than one table.
After Commerce Instance Creation
Commerce Instance Creation creates your database for you. During the development process there are times when it makes sense to go all the way back to this initial database image, so it is best to take a backup after Commerce Instance Creation and keep it until go-live. I take this backup after I’ve made a few configuration tweaks (like switching from circular logging to archive logging) so that I have a known good restore point.
Overall backup and recovery strategy
Just like any good DBA, Commerce DBAs have to have a basic backup and recovery strategy that comes out of the recovery requirements and the resources available. Generally for Dev and QA databases, I like to be able to restore to any point in the last two weeks, retaining 2-3 weekly full images on disk and the transaction logs to restore to any point from two weeks ago forward. Prod is more dependent on recovery times how often you do full or incremental backups, but frequently (though not always) have a lower period for retaining things (on prod, it’s very rare to have to or want to restore to a point in time greater than 24 hours ago). Stage backups should be taken at the same time as prod – due to stagingprop, if you restore prod, you also have to restore stage to the same point in time, or at least a point in time that is between the same stagingprops as prod.
It can obviously be more complicated than that, but that’s the general gist.
Oh, and personally, I’m not a fan of Deltas, and don’t use them. In the DB2 world, for a delta to even work, you frequently have to have all deltas since the last full, and if you add in incrementals and managing three types of images, it just gets so complicated, and error prone – if I’m missing one delta that I need, all those after it are useless.
And do I have to say that you must fully test your recovery strategies so you know that you have all the files you need AND that you know how to do the type of restore you need.
Before a FixPack(DB2 or Commerce) or Feature Pack(Commerce)
Technically, you could just use your backups and logs from your normal recovery strategy for this, but it is nice to have a line in the sand of exactly what point you need to restore to and is also nice to not have to roll forward through any logs. Also it’s rare, but if things really go south on a DB2 FixPack, you’ll be very glad to have this.
After FixPack(DB2 or Commerce) or Feature Pack(Commerce)
Again, you can technically use your backups from your normal recovery strategy, but it’s nice not to have to roll forward through whatever changes the Fix or Feature Pack made. If you have to do a normal restore for other reasons it’s nice to have a backup image that includes any drastic changes made.
Before other data-related changes
Always have a backout plan for everything you do. Better yet, have two. Even when I do a simple update to a single row, I first export that row (to have a before-version) and also note the time (in case I need to restore to before that change). Some changes like deletes affect tables that you might not expect, so if the change is a delete or affects more than one table, it’s good to take a backup to be sure you can restore to the right point in time easily and quickly. I also take backups before and after doing restores (from databases in other environments) because restores can over-write log files, and sometimes getting back to whatever crummy state made you do the restore is actually the less worse of two options.
So yes, I backup my databases quite frequently, and yes, it sounds a bit paranoid, but I can’t sleep at night unless my backups are in order.
