Protecting Your Data
Databases must survive hardware failures, software bugs, and human errors. Recovery mechanisms ensure that committed data is never lost and the database can return to a consistent state after a failure.
Types of Backups
Write-Ahead Logging (WAL)
Before modifying data, write the change to a LOG file:
1. BEGIN TRANSACTION
2. Write "UPDATE Accounts SET Balance=500 WHERE ID=1" to log
3. Write "UPDATE Accounts SET Balance=700 WHERE ID=2" to log
4. COMMIT (flush log to disk)
5. Apply changes to actual data files
If crash occurs between step 4 and 5:
→ Log is replayed on recovery
→ Changes are applied even though they weren't in data files yet
Recovery Process
After a crash:
1. Check transaction log for committed transactions
2. REDO: Reapply committed changes that weren't flushed to disk
3. UNDO: Roll back uncommitted changes that were partially applied
4. Database is now in a consistent state