Database Data Recovery

Database Recovery is the process of taking backup of the database and the data to a consistent state. This may include backup of lost data up to the point of the event (e.g. system crash).

There are several situations in which a transaction may not reach a commit or abort point.

• An operating system crash can terminate the DBMS processes
• The DMS can crash
• The system might lose power
• A disk may fail or other hardware may fail.
• Human error can result in deletion of critical data.

In any above situations, data in the database may become inconsistent or lost.
Data Engineers have expertise with all database types and RDBMS systems including MySQL, Oracle, Access and we can recover data very efficiently.

Two methods are discussed here: Manual Reprocessing and Automated Recovery.

Manual Reprocessing

• In a Manual Reprocessing recovery approach, the database is periodically backed up (a database save) and all transactions applied since the last save are recorded
• If the system crashes, the latest database backup set is restored and all of the transactions are re-applied (by users) to bring the database back up to the point just before the crash.
• Several shortcomings to the Manual Reprocessing approach:

1. Time required to re-apply transactions
2. Transactions might have other (physical) consequences
3. Re-applying concurrent transactions in the same original sequence may not be possible.

Automated Recovery with Rollback / Rollforward

• As with the manual recovery approach we also make periodic backups of the database (time consuming operation).
• In the Automated Recovery approach, we introduce a Log file – this is a file separate from the data that records all of the changes made to the database by transactions.
• Also referred to as a Journal.
• This transaction log Includes information helpful to the recovery process such as: A transaction identifier, the date and time, the user running the transaction, before images and after images
• Before Image: A copy of the table record (or data item) before it was changed by the transaction.
• After Image: A copy of the table record (or data item) after it was changed by the transaction.
• Rollback: Undo any partially completed transactions (ones in progress when the crash occurred) by applying the before images to the database.
• Rollforward: Redo the transactions by applying the after images to the database. This is done for transactions that were committed before the crash.
• The Automated Recovery approach uses both rollback and rollforward to restore the database.
• In the worst case, we would need to rollback to the last database backup point and then rollforward to the point just before the crash.