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.
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?