A copy of SQL Server data taken at a specific point in time to enable recovery in the event of data loss, corruption, hardware failure, or disaster.
- SQL Server supports four primary backup types: Full (complete database copy), Differential (changes since the last full backup), Transaction Log (log records for point-in-time recovery), and File/Filegroup (specific file groups).
- A well-designed backup strategy defines Recovery Point Objective (RPO) and Recovery Time Objective (RTO): RPO represents the maximum acceptable data loss, while RTO defines the maximum tolerable downtime.
- Transaction log backups are required for databases in Full or Bulk-Logged recovery models: They are essential for minimizing RPO.
- Backups should be stored in multiple locations: Local, off-site, and cloud storage options such as Azure Blob Storage and Amazon S3 help protect against site-level failures.
- DBAs must regularly test restores to verify backup integrity: A backup that cannot be restored is not a backup.
- Relevant Idera tools: SQL Safe Backup provides compressed, encrypted backups with instant recovery capabilities and centralized policy management.
- Related terms: Restore, Transaction Log, Full Recovery Model, RPO, RTO, SQL Safe Backup.
