New table-munching worm ravages Iranian biz databases

Re: Well-managed backups - what a good idea.

A second "production" system would be the one that you have provisioned for disaster recovery (DR). That's the one in a separate building, with its own electricty, etc supply. Connected only by optic fibre or a directional, wireless link.

How else does one cope with e.g. a physical catastrophe that might take days to weeks to resolve?

The DR system doesn't have to have 100% of the capabilities of the live system. It "only" needs to provide core business functions. You must be sure that it can, so you have to test it irregularly but at least several times a year; perhaps running a full "DR drill" once every year to 18 months if the plan isn't exercised out of necessity.

As for rogue RDBMS transactions, most engines support transaction logging. Those seeking to milk the last bits of performace out of the system may not have turned it off or never enabled it. The performance hit from transaction logging, when properly configured is a few percent. That is well worth it for protecting the integrity of enterprise data.

The vanilla procedure for recovery from a committed rogue transaction is to restore from the previous backup and then roll forward on the transactions up to the one which was rogue. It *may* be safe to roll forward on subsequent transactions but that is not the general case.

If you don't have a transaction log, then restoring from teh previous backup required "re-keying" all the data. Which is more likely not "possible" nowadays with EDI and web-presence. It may be possible to replay electronic transactions but their ORDER is significant in most EDI systems to preserve e.g. order numbers. Otherwise shipments, etc using such sequence numbers will become lost.