Database Backups and Recovery

In this lesson, you will learn how to implement a recovery strategy that protects your data integrity when the entire database system suffers a catastrophic failure.

The Foundation of Recovery

So far, we have focused on COMMIT and ROLLBACK to handle data consistency during active sessions. However, these commands only protect you against logical errors or concurrency issues. If your server’s hard drive fails or the data center loses power, your active transactions are lost. To survive these system-level failures, we must implement a Backup and Recovery strategy.

The Backup Lifecycle

A backup is essentially a snapshot of your database at a specific point in time. When you perform a backup, the database management system captures the structure and data of your tables and stores them in a separate file.

Recovery is the process of using these files to restore your database to its last known healthy state. A standard strategy involves two parts:

1. Full Backups: A complete copy of the entire database.
2. Transaction Log Backups: Files that record every COMMIT since the last full backup.

Implementing the Strategy

While specific commands vary by system, the logic remains the same. You take a full backup daily and rotate your transaction logs frequently. If a failure occurs at 2:00 PM, you restore the last full backup (from the night before) and then "replay" the transaction logs to recover every single COMMIT made up until the moment of the crash.

-- Conceptual example of initiating a backup
BACKUP DATABASE StoreInventory 
TO DISK = 'C:\Backups\StoreInventory_Full.bak';

-- If a system-level failure occurs, you perform a restore:
RESTORE DATABASE StoreInventory 
FROM DISK = 'C:\Backups\StoreInventory_Full.bak'
WITH RECOVERY;

Protecting Integrity

Without a recovery plan, a hardware failure can result in total data loss. By separating your data from the server’s physical storage, you ensure that your business logic and entity integrity remain intact even if the primary server becomes unusable. Always keep your backup files in a separate, secure physical location to ensure that the recovery files are not destroyed alongside the database server itself.

Key Takeaway: A robust recovery strategy combines periodic full snapshots with frequent transaction logs to ensure your data can be fully restored even after a total system failure.