Data Recovery Digest

Do-It-Yourself Windows File Recovery Software: A Comparison

results »

How Do Database Rollbacks Work?

Life isn’t perfect. That’s even more true when it comes to technology. The question isn’t if something will go wrong, it’s when. Mistakes and errors are bound to happen eventually. That’s why backups are so important. If you don’t accidentally delete your data, a virus might do. Or if the virus doesn’t get it, a natural disaster might physically destroy your system.

When it comes to databases, things get even more complex. They aren’t single pieces of data, like a photo or a video. A database is a huge collection of information and most of the time other systems are reliant on pulling data from it. It’s important that your database is stable.

If something does go wrong with your database, you will need to perform a database rollback. A rollback is when you return the database to the state it was in before. To ensure the data remains accurate and consistent (or in other words, has integrity), rollbacks are vital. They mean that the database can be restored to a clean copy in a previous version, even if an erroneous operation was performed.

For example, if a user changes in a field in the database without saving, this is saved in a temporary transaction. The unsaved data can be queried, but it isn’t saved until committed. If a user decides not to save, the rollback command discards all of this data.

An erroneous operation could be a database server crash, caused by an automatic transaction, or something from human error. If the database is poorly protected, it could even be caused by a power outage.

Put simply, a database rollback turns back the clock and restores a working version of the database. It can occur when a user changes data, decides they don’t want to update the record, and cancel it. It can also occur to correct failure.

A cascading rollback is more involved. These are needed when a tier one database transaction fails. Many other transactions will be reliant on those at tier one, thus a failure at the top level will impact massively down the chain. Ideally, a cascading rollback shouldn’t be used because you are restoring lots of transactions to earlier stages that they might not need to be in.

If your database is in SQL, you can run the ROLLBACK command. This will cause all changes since the BEGIN WORK command ran to be discarded. It will also remove any save points that have been put in place.

Rollbacks can be automatically carried out after a server or database crash. When the system restarts, all transactions in the log are reviewed. All of the pending transactions are rolled back, reverting the database to the state it was in before the crash.

The best approach is to take backups of your database at regular intervals which can be restored to in a disaster, ensuring you have redundancy in where that backup is stored. Don’t rely on the rollback/commit commands, which are geared towards day-to-day use rather than a full-scale recovery.


No comments yet. Sign in to add the first!