In this lesson, you will learn how to create a point-in-time snapshot of your database without interrupting live transactions or sacrificing data integrity.
The Challenge of Hot Backups
In a busy production environment, taking a "cold" backup—where you shut down the database—is often impossible because it causes downtime. However, simply copying data files while the database is running is dangerous. If you copy a data page while the system is halfway through updating it, you might end up with a "torn page," where half the data reflects the old state and half reflects the new state. This results in a corrupt, inconsistent snapshot.
How Hot Backups Maintain Consistency
To create a "hot" (or online) backup, the database uses the Write-Ahead Log (WAL) to maintain consistency. When you initiate a hot backup, the database does not pause transactions. Instead, it performs the following mechanism:
- Marking the Start: The database records a specific log sequence number (LSN) marking the exact moment the backup began.
- Copying Data Files: It begins copying the raw data files. Because these files change as transactions modify data, the copy will be a "fuzzy" mixture of different transaction states.
- Capturing Changes: As the database engine continues to run, it captures all incoming changes in the redo logs.
- Reconciliation: When you eventually restore this backup, the database engine uses the LSN recorded in step one. It replays the redo logs from that point forward to "roll forward" the fuzzy data files, ensuring all transactions are applied correctly until the database reaches a single, consistent state.
Conceptual Workflow
Think of the process like this:
-- 1. Notify the engine we are starting a backup
ALTER DATABASE BEGIN BACKUP;
-- 2. Copy the data files (the 'fuzzy' files)
-- OS level: cp /data/* /backup_destination/
-- 3. The database continues to record changes to the WAL
-- 4. Notify the engine the copy is finished
ALTER DATABASE END BACKUP;
Even though the files you copied weren't consistent at the moment of the copy, the Redo Log provides the missing instructions needed to reach a consistent state later. By combining these fuzzy files with the logs, you guarantee that the snapshot represents a single, valid point in time.
Key Takeaway: Hot backups remain consistent by combining "fuzzy" data file copies with the Write-Ahead Log, allowing the engine to replay transactions and reach a valid state during restoration.