Backups are your last line of defense against data loss, corruption, and ransomware. SQL Server provides full, differential, and transaction log backups — combined with the right recovery model, they enable point-in-time recovery (PITR) aligned with business RPO/RTO targets.

Recovery Models 

  ALTER DATABASE MyApp SET RECOVERY FULL;
-- SIMPLE: minimal log, no log backups, no PITR
-- FULL: complete log chain for PITR
-- BULK_LOGGED: minimal logging for bulk ops — log backups still required
Model Log Backups PITR Typical Use
SIMPLE No No Dev, disposable data
FULL Yes Yes Production OLTP
BULK_LOGGED Yes (with gaps during bulk) Partial during bulk ETL staging windows

Switching to FULL does not start logging chain until first full backup.

Full Backup 

  BACKUP DATABASE MyApp
TO DISK = N'\\backup-share\sql\MyApp_full.bak'
WITH
    COMPRESSION,
    CHECKSUM,
    INIT,
    STATS = 10,
    NAME = N'MyApp-Full Backup';
GO
  • COMPRESSION — smaller files, more CPU (usually worth it)
  • CHECKSUM — detect media corruption
  • INIT — overwrite existing file (careful with file names)

Differential Backup

Captures changes since last full backup — faster for large databases:

  BACKUP DATABASE MyApp
TO DISK = N'\\backup-share\sql\MyApp_diff.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM, STATS = 10;

Restore chain: full → latest differential → log backups after differential.

Transaction Log Backup 

  BACKUP LOG MyApp
TO DISK = N'\\backup-share\sql\MyApp_log_202406131400.trn'
WITH COMPRESSION, CHECKSUM, STATS = 5;

Required for PITR in FULL recovery. Schedule every 15–60 minutes based on RPO.

Monitor log space — without log backups, log file grows until disk full:

  DBCC SQLPERF(LOGSPACE);
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases WHERE name = N'MyApp';

Backup Encryption 

  BACKUP DATABASE MyApp
TO DISK = N'\\backup-share\sql\MyApp_enc.bak'
WITH COMPRESSION, ENCRYPTION (
    ALGORITHM = AES_256,
    SERVER CERTIFICATE = BackupCert
);

Protects backup files at rest — essential for off-site and cloud storage.

Restore Full Backup 

  -- New database from backup
RESTORE DATABASE MyApp_Restore
FROM DISK = N'\\backup-share\sql\MyApp_full.bak'
WITH
    MOVE N'MyApp' TO N'D:\Data\MyApp_Restore.mdf',
    MOVE N'MyApp_log' TO N'E:\Log\MyApp_Restore_log.ldf',
    REPLACE,
    STATS = 10;

Use MOVE when file paths differ from source server.

Point-in-Time Recovery 

  -- Step 1: Full backup (NORECOVERY keeps restoring)
RESTORE DATABASE MyApp FROM DISK = N'\\backup-share\sql\MyApp_full.bak' WITH NORECOVERY;

-- Step 2: Differential (if available)
RESTORE DATABASE MyApp FROM DISK = N'\\backup-share\sql\MyApp_diff.bak' WITH NORECOVERY;

-- Step 3: Log chain
RESTORE LOG MyApp FROM DISK = N'\\backup-share\sql\MyApp_log1.trn' WITH NORECOVERY;
RESTORE LOG MyApp FROM DISK = N'\\backup-share\sql\MyApp_log2.trn' WITH NORECOVERY;

-- Step 4: Recover to specific time
RESTORE LOG MyApp FROM DISK = N'\\backup-share\sql\MyApp_log3.trn'
WITH RECOVERY, STOPAT = N'2024-06-01T14:30:00';

STOPAT must fall within restored log range — test with RESTORE HEADERONLY.

Verify Backups 

  RESTORE VERIFYONLY FROM DISK = N'\\backup-share\sql\MyApp_full.bak';

RESTORE HEADERONLY FROM DISK = N'\\backup-share\sql\MyApp_full.bak';

RESTORE FILELISTONLY FROM DISK = N'\\backup-share\sql\MyApp_full.bak';

VERIFYONLY checks structure — not a full restore test. Monthly restore drill to isolated server is mandatory.

Copy-Only Backups 

  BACKUP DATABASE MyApp TO DISK = N'C:\adhoc\MyApp_copyonly.bak'
WITH COPY_ONLY, COMPRESSION;

Does not break log chain — use for ad-hoc snapshots before risky migrations.

Maintenance Schedule Template

Backup Frequency Retention
Full Weekly (or daily) 4 weeks local, 1 year archive
Differential Daily 2 weeks
Log Every 15 min 7 days

Automate with SQL Agent, Ola Hallengren scripts, or Azure Backup for IaaS/PaaS.

Always On and Backups 

  -- Prefer secondary for backup I/O
ALTER AVAILABILITY GROUP [AppAG] MODIFY (
    AUTOMATED_BACKUP_PREFERENCE = SECONDARY
);

Backups on primary still work — secondary reduces load on write primary.

Common Mistakes

Mistake Consequence
SIMPLE recovery in production Cannot PITR after user error
Never testing restore Backups corrupt until needed
Same disk as data files Single failure loses data and backups
No CHECKSUM Silent corruption undetected
Truncating log manually (TRUNCATEONLY) Breaks chain — avoid unless emergency

Performance Tips

  • Enable backup compression — less I/O to network share
  • Striped backups for large DBs — parallel I/O:
  BACKUP DATABASE MyApp
TO DISK = N'\\share\MyApp_1.bak', DISK = N'\\share\MyApp_2.bak', DISK = N'\\share\MyApp_3.bak'
WITH COMPRESSION, CHECKSUM;
  • Backup to dedicated network — not application server disk
  • Use BUFFERCOUNT and MAXTRANSFERSIZE tuning for very large databases

Production Scenario: Oops DELETE Recovery

  1. Switch to FULL recovery if not already
  2. Take tail-log backup:
  BACKUP LOG MyApp TO DISK = N'\\share\MyApp_tail.trn' WITH NO_TRUNCATE;
  1. Restore full + diff + logs to minute before DELETE
  2. Restore tail log to separate database; merge lost rows

Document break-glass procedure with contact tree and approval steps.

Troubleshooting

Error Cause Fix
Log backup fails — log full Long running transaction, replication, mirror Find log_reuse_wait_desc
Restore fails — in use Active connections ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Backup slow Network, compression CPU, VSS Striped backup, local staging copy
Cannot restore — different version Backup from newer SQL Restore on same or newer version 
  SELECT r.session_id, r.command, r.percent_complete, r.estimated_completion_time
FROM sys.dm_exec_requests r
WHERE r.command LIKE N'BACKUP%';

Untested backups are wishful thinking — automate, verify, and restore monthly to meet production SLAs.