The Wrong Way to Think About Backups
Most teams design their backup strategy and then figure out what they can recover to. That's backwards. Start with your recovery requirements and design the backup strategy to meet them. The question isn't "how often do we back up?" It's "how much data can we afford to lose?"
Understanding RPO and RTO
RPO (Recovery Point Objective): The maximum acceptable data loss measured in time. If your RPO is 1 hour, you must back up at least every hour. If disaster strikes at 10:45, you can lose at most 45 minutes of data.
RTO (Recovery Time Objective): The maximum acceptable downtime. If your RTO is 4 hours, you must be able to restore operations within 4 hours of the disaster declaration.
These aren't technical constraints—they're business decisions. The business decides what data loss and downtime cost. IT estimates what it costs to meet those requirements. Then you negotiate.
Backup Types Explained
Every backup strategy combines these types:
- Full backup: Complete copy of all data. Baseline for any restore. Run weekly for most systems.
- Differential backup: Everything that changed since the last full backup. If Monday you ran a full backup, Tuesday differential captures Monday→Tuesday changes, Wednesday differential captures Monday→Wednesday changes (grows over time).
- Transaction log backup: SQL Server-specific. Captures the transaction log, enabling point-in-time recovery. Essential for full recovery model. Run every 15-60 minutes depending on RPO.
Recovery Models
Full: All transactions logged. Supports point-in-time recovery. Required for production OLTP databases. Highest log storage cost.
Bulk-Logged: Minimal logging for bulk operations (BCP, SELECT INTO, index creation). Faster bulk loads but cannot do point-in-time recovery for the time period when bulk operations ran.
Simple: Log truncated after checkpoint. No log backups required. Cannot do point-in-time recovery. Only appropriate for read-only or development databases. Many teams don't realize their "simple" dev database doesn't reflect production behavior.
Designing Your Backup Schedule
Example for a 24-hour RPO system:
- Sunday: Full backup (off-peak, after weekly maintenance window)
- Monday–Saturday: Transaction log backups every 15 minutes
- Consider differential backups mid-week if full backup takes too long
Example for a 1-hour RPO system:
- Daily full backup at midnight
- Differential every 6 hours (6am, noon, 6pm)
- Transaction log backups every 15 minutes
Testing Your Backups
A backup you haven't tested is not a backup. It's a hope. Schedule regular test restores:
-- Verify backup file integrity without restoring
RESTORE VERIFYONLY FROM DISK = '\\backupserver\mydb_full.bak';
-- Restore to a test server (never restore over production)
RESTORE DATABASE MyDB_Test
FROM DISK = '\\backupserver\mydb_full.bak'
WITH MOVE 'MyDB_Data' TO 'D:\Data\MyDB_Test.mdf',
MOVE 'MyDB_Log' TO 'E:\Logs\MyDB_Test.ldf',
RECOVERY;
-- Verify database integrity after restore
DBCC CHECKDB ('MyDB_Test');Offsite Backup: The Backup You Actually Need
Backups on the same server as your database are backups of a different kind of risk. Fire, ransomware, accidental deletion—these destroy the database and its backups together. Cloud backup (Azure Blob Storage, Amazon S3, or a remote file share) is essential for any system you actually care about recovering.
SQL Server's native backup to URL feature makes this straightforward:
BACKUP DATABASE MyDB TO URL = 'https://mystorage.blob.core.windows.net/mybackups/MyDB_full.bak'
WITH FORMAT, COMPRESSION, CHECKSUM;The Retention Question
How long to keep backups depends on compliance requirements, business needs, and storage cost. Minimum:
- At least one full backup from each week for 4 weeks
- One full backup from each month for 12 months (for quarterly audits)
- Year-end backup retained indefinitely for financial compliance
Storage is cheap. The cost of not having a 2-year-old backup when you need it is not. Demand it. Storage is cheap. Data loss is not.