How can I recover from a backup but include many changes from a dynamic site? After the Drupageddon disaster, I've discovered that my backup plan is not sufficient. I have daily, weekly, monthly and yearly DB and file backups thanks to backup_migrate and backup_migrate_prune, but my site is a dynamic one with many users making posts daily. So I was able to easily find a DB backup from pre-Oct 15th to restore to, but I had no easy way to incorporate the thousands of legitimate changes that had occurred on the site since then. 

I recognize that it's probably too late for this time, but I'm thinking about how to make sure I'm not screwed in the future. Is there any good method to accomplish this sort of thing? A module that logs all database transactions so they can be individually inspected and reapplied if they're deemed legitimate? Or anything at all better than either loosing weeks of user data or doing a manual diff of the current and known-good databases and praying that I didn't miss something?
A module that logs all database transactions so they can be individually inspected and reapplied if they're deemed legitimate?


Where it would log to? Database? Impossible, it would kill your server. Filesystem? It's slow. And it's big. Another thing worth noting is that if you would want to manually approve / disapprove each query, you can either:


Inspect them carefully, and you already told us there is a lot of them, so it could take you up to hours per one edit, if your content types are complicated enough - not nice on dynamic site.
Inspect them fast, probably making your data inconsistent.


Last but not least, if you will add that as a module, anyone with proper permissions will be able to disable that module, making your efforts void.

Modules that should be able to help some are:


Drupal Tiny-IDS
PHPIDS


Sadly, as far as I understand, neither would help in this particular case.

What I would do is:


Hourly backup. If possible, done as a snapshot of virtual machine, because that's fast and unobtrusive.
Mail about each new user, new role assignment, and password change. Sadly, if no one monitors that mail, no one can help.