Backup and Restore — Full, Differential and Transaction Log Backups

A backup strategy is the difference between a recoverable incident and a catastrophic data loss. SQL Server supports three backup types that work together: full backups (complete database snapshot), differential backups (changes since last full), and transaction log backups (all committed transactions since last log backup, enabling point-in-time recovery). The recovery model setting controls which backup types are available and how much data can be recovered after a failure.

Backup Strategy Implementation #

-- ── Full backup ────────────────────────────────────────────────────────────
BACKUP DATABASE BlogApp
TO DISK = 'C:\Backups\BlogApp_Full_20240715_0200.bak'
WITH COMPRESSION,          -- compress the backup file
     CHECKSUM,             -- write checksum for later verification
     STATS = 10;           -- show progress every 10%
GO

-- ── Differential backup (requires prior full backup) ──────────────────────
BACKUP DATABASE BlogApp
TO DISK = 'C:\Backups\BlogApp_Diff_20240715_1400.bak'
WITH DIFFERENTIAL,
     COMPRESSION,
     CHECKSUM,
     STATS = 10;
GO

-- ── Transaction log backup (FULL recovery model required) ─────────────────
BACKUP LOG BlogApp
TO DISK = 'C:\Backups\BlogApp_Log_20240715_1400.trn'
WITH COMPRESSION, CHECKSUM, STATS = 10;
GO

-- ── Verify backup integrity before relying on it ──────────────────────────
RESTORE VERIFYONLY
FROM DISK = 'C:\Backups\BlogApp_Full_20240715_0200.bak'
WITH CHECKSUM;
-- Returns "The backup set on file 1 is valid." if OK

-- ── Point-in-time restore sequence ────────────────────────────────────────
-- 1. Restore the most recent full backup (NORECOVERY keeps DB in restoring state)
RESTORE DATABASE BlogApp_Restored
FROM DISK = 'C:\Backups\BlogApp_Full_20240715_0200.bak'
WITH NORECOVERY,
     MOVE 'BlogApp_Data' TO 'C:\SQLData\BlogApp_Restored.mdf',
     MOVE 'BlogApp_Log'  TO 'C:\SQLData\BlogApp_Restored_log.ldf';

-- 2. Apply the differential (if available):
RESTORE DATABASE BlogApp_Restored
FROM DISK = 'C:\Backups\BlogApp_Diff_20240715_1400.bak'
WITH NORECOVERY;

-- 3. Apply transaction log backups up to the point in time:
RESTORE LOG BlogApp_Restored
FROM DISK = 'C:\Backups\BlogApp_Log_20240715_1500.trn'
WITH NORECOVERY;

-- Final log — stop at the exact moment before the incident:
RESTORE LOG BlogApp_Restored
FROM DISK = 'C:\Backups\BlogApp_Log_20240715_1545.trn'
WITH RECOVERY,    -- bring database online
     STOPAT = '2024-07-15 15:42:00';  -- stop just before the bad event

-- ── Backup to Azure Blob Storage ──────────────────────────────────────────
-- Requires: CREDENTIAL with SAS token or Managed Identity
BACKUP DATABASE BlogApp
TO URL = 'https://mystorageaccount.blob.core.windows.net/backups/BlogApp_Full.bak'
WITH COMPRESSION, CHECKSUM, STATS = 10;

-- ── Recommended backup schedule for BlogApp production ────────────────────
-- Sunday 02:00  — Full backup
-- Mon-Sat 02:00 — Differential backup
-- Every 15-30 min (business hours) — Transaction log backup
-- RPO: up to 15-30 minutes of data loss maximum
-- RTO: restore depends on full backup size + differential + logs applied

Backup Type Comparison #

Common Mistakes #

Mistake 1 — Full recovery model without log backups (log file fills disk) #

❌ Wrong — FULL recovery but no log backup job; transaction log grows unboundedly until disk is full.

✅ Correct — schedule log backups every 15-30 minutes; log is truncated (reused) after each backup.

Mistake 2 — Never testing restores (backup may be corrupt or incomplete) #

❌ Wrong — backups run nightly but restore has never been tested; corruption discovered during an emergency.

✅ Correct — monthly restore drill to a test environment; verify data integrity and application functionality.