Applies toSQL Server 2016 and later on-premises and in cloud VMs; backup to disk, SMB, Azure Blob (URL) and S3-compatible storage; Azure Backup for SQL Server in Azure VMs
Tiers from business impact
| Tier | Example | RPO | RTO |
|---|---|---|---|
| 1 | Payments, order entry | Minutes | < 1 hour |
| 2 | Internal line-of-business apps | ≤ 1 hour | Same day |
| 3 | Reporting, dev/test | 24 hours | Days |
Agree these with application owners. RPO/RTO for tier 1 may also need HA/DR (AGs, log shipping), not only backups (see the DR comparison post).
Schedules from RPO
| Tier | Full | Differential | Log |
|---|---|---|---|
| 1 | Weekly or daily | Daily | Every 5–15 min |
| 2 | Weekly | Daily | Every 15–60 min |
| 3 | Daily or weekly (SIMPLE recovery may be fine) | Optional | None if SIMPLE |
Log backup frequency sets the RPO for backup-based recovery. Recovery model must be FULL for log backups.
Restore time from RTO
Restore time = full + latest differential + all logs since. Measure it:
- Large databases: more frequent fulls/differentials shorten restore chains.
- Fast restore targets and striping help (see the backup speed post).
- If restore time can't meet RTO, backups alone aren't enough; add a standby.
Where backups live
| Copy | Purpose |
|---|---|
| Local/near copy (short retention) | Fast restores |
| Off-server copy in another location/region | Server or site loss |
| Immutable copy (WORM storage, immutable blob policies, S3 Object Lock, vault lock) | Ransomware and malicious deletion |
Follow a 3-2-1 style approach: several copies, different media/locations, at least one immutable or offline.
Protect the backups
- Backup encryption with certificates backed up separately (see the TDE certificate Sev1 post).
- Separate credentials: the SQL service account shouldn't be able to delete immutable copies.
- Monitor backup jobs; alert when any database misses its schedule.
-- Databases without a log backup in the last hour (FULL recovery)
SELECT d.name, MAX(b.backup_finish_date) AS last_log
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b ON b.database_name = d.name AND b.type = 'L'
WHERE d.recovery_model_desc = 'FULL' AND d.database_id > 4
GROUP BY d.name
HAVING MAX(b.backup_finish_date) < DATEADD(hour, -1, GETDATE()) OR MAX(b.backup_finish_date) IS NULL;
Prove it
- Automated restore tests (e.g. weekly to a test server) with
DBCC CHECKDBon the restored copy. - Record restore durations vs RTO.
- Annual DR exercise for tier 1.
Caution
A backup you haven't restored is a hope, not a backup. Make test restores a scheduled job with alerts, not a yearly manual task.
Checklist
- RPO/RTO per tier agreed
- Schedules derived from RPO
- Restore chain time meets RTO
- Off-site and immutable copies
- Encryption keys and certificates protected separately
- Missed backup alerts and automated restore tests