Question about backup - can one delete a transaction?

[cravincreeks]

I'm an Access user who's looking to have a RDBMS with a more robust back up system. We have a rather big project coming up and so far, we're planning to use Accesss. But I'm thinking about making the switch to MySQL. It will be in a multi-user environment (however, only a few users will be writing data). But many others will be reading.

Overall, I'm rather inexperienced when it comes to backing up transaction logs. This is my question. Say a user accidentally deletes half of the records in a table. Then more records are added and many are updated. Then three weeks later, somebody realizes that half of the records have been deleted (the accidental deletion mentioned above). So is it possible to go back in the transaction log and find the delete 'transaction' in which the said records were deleted? Then is possible to 'undo' that deletion? What about all of the valid inserts and updates that were made after the accidental deletions. Will those be preserved? In other words, is it possible to undo individual tranactions/operations without affecting the transactions made after then?

Thanks

AZ

 

[Bastien]

What we do is to add another column called 'record_deleted' If the user wants to delete a record, this gets updated to 1 from 0 (its a tinyint field). Any of our scripts are then written to only search on record_deleted = 0 to avoid the 'deleted' records.

At some point, a senior admin will look at the records and decide whether to dump the deleted records (I would suggest a copy to another table/db) and remove them from the main table. Then if a restore is needed, you either undelete the record by changng the record_deleted flag back to 0, or copy the record back in from the discard table and change the status.

This in no way alleviates the need for proper back ups and testing of back ups should the db crash, but from an application perspective, its simple and easy to set up.

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...