For a web application I was asked to design a backup strategy. At the moment there are hourly full backups (database and files). In light of the GDPR, and because it's just the way it should work, recent changes happening between two backups, should be restored as well.

For example:

14:00 Create backup
14:05 John deletes his account
14:34 Server crashes
14:44 Backup restored - User 1 is not deleted

I believe this is not uncommon and there should be some kind of best practice. For example a second database on a second server which stores incremental changes since the last backup and restores them after a crash. These incremental changes can be deleted after every successful backup. But in my opinion that is rather complicated.

While writing this I thought maybe replication is the way to go here?

Or is there another method I haven't thought of?

  • 1
    Why not just restore the transaction logs along with the backup? – Dan Pichelman Mar 27 '18 at 14:42
  • As well as full and incremental backups, some databases have point in time recovery, but this can consume quite a bit of disk space. – Robbie Dee Mar 27 '18 at 15:15
  • 1
    @DanPichelman I think the assumption is that the deletion of the account happens between backups, which means the transaction log is lost along with the rest of the data. – Blrfl Mar 27 '18 at 18:54
  • @Blrfl yeah that is my problem, thanks for clarifying! – Tim Mar 28 '18 at 7:09
  • About all you can do is use your database's HA features to make sure the transaction log survives. There's no magic bullet for a single system. – Blrfl Mar 28 '18 at 11:42

Solution 1: use the database features

Many database systems use logs to ensure transactional consistency. By looking at your DBMS documentation you may find a way to use such logs for the purpose of your restore.

For example, on an Oracle RDBMS you have so called redo logs , that can be archived automatically and that you can put on another disk. In case of a crash you'll restore from a backup, and then apply the redo logs until the latest completed change. Only incomplete transactions would be lost (this means that you shall inform the user of the successful deletion of his/her account after the successful commit, and not before).

Build an event log

The other alternative, is to manage a kind of redo log at application level.

The idea is based on an event queue: your app doesn't write directly to the db but generates events/commands. These events are logged before being processed.

In case of a crash, you restore the db, find back the latest event that was processed therein, and then simply restore the subsequent events in the log on the event queue and let your event processor reprocess them.

Sounds easy, but it requires your app to be designed to work asynchronously with the event and the event feedback(i.e. another kind of event).

  • Thanks for your help! I already have an event log, but it is saved in the database, too. – Tim Mar 28 '18 at 7:07
  • My problem I have is not so much to restore a corrupted database, but what if someone hacked the server and just deletes it? Or the server burns down or the data center drowns or whatever? – Tim Mar 28 '18 at 7:08
  • 2
    @Tim you can't solve it all with software only. Because if someone could hack and wipe everything, the same hacker might probably have the same accesses that your app and could corrupt or delete everything your app produces, including any log. You'd need to carefully review your network architecture as well. And the only way to protect against a burning datacenter is to copy the logs on a remote server in another datacenter. You could even use some realtime mirroring of disks between two buildings (yes datacenter specialists can do that!) – Christophe Mar 28 '18 at 7:33

As mentioned, serious databases have a redo-log tat can be executed partially or completely, as desired.

Note however, that depending on the nature of the action you are trying to skip, your database might get corrupted by the process:
Imagine that Joe deletes a record [this is the action that you want to recover]. In the following transactions, the non-existance of this record has potential consequences, for example when select statements don’t contain it, sums are calculated differently, business logic makes different decisions, etc., resulting in further consequences. You will get quickly into deep trouble.

Maybe a new user picked ‘John’ as username as it was free. Are you going to disallow this retroactively because you recovered the old ‘John’? What if the new ‘John’ ordered stuff and already paid for it?

Depending on the nature of the action to be undone, this might or might not be an issue, but you always need to carefully consider it.

  • The user is not really deleted, but a flag is set. After some time he will get deleted nevertheless but my problem is that there are circumstances where you can't access the server anymore because of some major outage. A redo log, I think, just helps when the database is corrupted but still available. – Tim Mar 28 '18 at 7:12

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.