SQL Server Backup Concepts: Strategy, Recovery Models & Best Practices

Three Types of Backups

1. Full Backup (Complete Database)

BACKUP DATABASE MyDatabase
TO DISK = 'D:\Backups\MyDatabase_Full_20240715.bak'
WITH COMPRESSION;

-- Properties:
// - Entire database (tables, indexes, config)
// - Largest backup type
// - Foundation for recovery
// - Required before differential/log backups
// - Frequency: Daily (usually overnight)

2. Differential Backup (Changes Since Last Full)

BACKUP DATABASE MyDatabase
TO DISK = 'D:\Backups\MyDatabase_Diff_20240715.bak'
WITH DIFFERENTIAL, COMPRESSION;

-- Properties:
// - Copies only changes SINCE last full backup
// - 10-30% of full backup size (much smaller)
// - Fast to create
// - REQUIRES full backup before it
// - Frequency: Multiple times per day

-- Recovery: Full backup + Last Differential

3. Transaction Log Backup (Granular Recovery)

BACKUP LOG MyDatabase
TO DISK = 'D:\Backups\MyDatabase_Log_20240715_1030.trn'
WITH COMPRESSION;

-- Properties:
// - Only committed transactions since last backup
// - Very small (minutes of data)
// - Very fast (seconds)
// - Point-in-time recovery (recover to exact moment)
// - ONLY in FULL or BULK-LOGGED recovery model
// - Frequency: Every 1-15 minutes

-- Recovery: Full + Differentials + Logs up to specific time

Recovery Models

RTO and RPO: Define Recovery Requirements

-- RTO (Recovery Time Objective): Time to recover
// Example: "We need to recover within 1 hour"
// Goal: Full backup + Diffs = 1 hour restore

-- RPO (Recovery Point Objective): Data loss tolerance
// Example: "We can afford to lose max 15 minutes of data"
// Goal: Backup transaction logs every 15 minutes

-- Strategy determines both:
// Full backup nightly + Diffs every 2 hours + Logs every 5 minutes
// RTO: ~30 minutes (restore full + recent diffs + logs)
// RPO: ~5 minutes (max data loss)

-- Mission-critical databases:
// RTO < 1 hour + RPO < 1 minute
// Requires: Frequent log backups + standby replica

Automated Backup Maintenance: New-sqmBackupMaintenanceJob

Example: Create Backup Job

-- PowerShell (using sqmBackupMaintenanceJob):
New-sqmBackupMaintenanceJob `
  -SqlInstance "SQL01" `
  -Database "MyDatabase" `
  -BackupPath "D:\Backups" `
  -FullBackupSchedule "Every day at 10:00 PM" `
  -DiffBackupSchedule "Every 2 hours" `
  -LogBackupSchedule "Every 15 minutes" `
  -RetentionDays 30 `
  -Compression $true `
  -VerifyBackup $true `
  -AlertOnFailure $true;

-- Result:
// - Full backup job: Every night
// - Differential backup job: Every 2 hours
// - Log backup job: Every 15 minutes
// - Old backups auto-deleted after 30 days
// - Verification and alerts configured
// - No manual script writing needed!

Advanced: sqmSQLTool Backup Automation

The sqmSQLTool (part of your PowerShell DBA toolkit) provides two powerful approaches for production backups: single-database backups via Invoke-sqmUserDatabaseBackup, and scheduled job creation via New-sqmOlaUsrDbBackupJob with centralized exclude-list management.

Single Database Backup: Invoke-sqmUserDatabaseBackup

For ad-hoc or immediate backups of individual databases without job scheduling:

-- One-time backup of a database
Invoke-sqmUserDatabaseBackup `
  -SqlInstance "SQL01" `
  -Database "ProductionDB" `
  -BackupType "Full";

-- Backups target FULL, DIFF, and LOG types
// - No job scheduling required
// - Direct, immediate execution
// - Useful for manual intervention, testing, or pre-maintenance backups

-- Reference: Invoke-sqmUserDatabaseBackup

Automated Scheduled Backups: New-sqmOlaUsrDbBackupJob

Create SQL Agent jobs for full, differential, and transaction log backups using the Ola Hallengren maintenance solution framework. All jobs automatically respect a centralized exclusion list.

-- One-command setup: Create backup jobs with exclude-table
New-sqmOlaUsrDbBackupJob `
  -SqlInstance "SQL01" `
  -Full `
  -Diff `
  -Log `
  -UseExcludeTable;

-- This command:
// 1. Initializes master.dbo.sqm_BackupExclude table (if needed)
// 2. Registers DDL trigger for automatic new-database detection
// 3. Creates SQL Agent jobs: FULL (nightly), DIFF (every 2 hrs), LOG (every 15 min)
// 4. All jobs use IsActive=1 (backup) / IsActive=0 (exclude) from the table
// 5. In AlwaysOn, jobs sync to all secondary replicas

-- Reference: New-sqmOlaUsrDbBackupJob

Managing the Backup Exclusion List

The core mechanism: master.dbo.sqm_BackupExclude table stores IsActive (backup/exclude flag) for every database. Jobs read this table at runtime—changes take effect on the next scheduled run without modifying jobs.

Automatic New-Database Detection

When you create a new database, the DDL trigger (trg_sqm_BackupExclude_AutoSync) fires automatically and adds the database to the exclusion list with IsActive=1 (will be backed up). No manual synchronization required.

-- Trigger workflow:
// 1. CREATE DATABASE MyNewDB
// 2. DDL Trigger fires → INSERT into master.dbo.sqm_BackupExclude
// 3. MyNewDB appears in Show-sqmBackupExcludeForm with IsActive=1
// 4. Next backup job run includes MyNewDB (unless you uncheck it)

-- Manual sync (if needed after deleted databases):
Sync-sqmBackupExcludeTable -SqlInstance "SQL01";

// Updates the table with all current databases
// Marks deleted databases as IsOrphaned=1 (read-only in GUI)

Non-Admin Database Access to Exclusion List

Grant DBA teams permission to manage the exclusion list without sysadmin rights:

Set-sqmBackupExcludePermission `
  -SqlInstance "SQL01" `
  -LoginName "DOMAIN\DBA-Team";

// Creates login, database user in master, and grants:
// - SELECT, INSERT, UPDATE on master.dbo.sqm_BackupExclude
// - Permission to run Show-sqmBackupExcludeForm and modify the GUI

Best Practices

• ☐ Use FULL recovery model for production (allows point-in-time recovery).
• ☐ Backup transaction logs frequently (every 5-15 minutes for critical databases).
• ☐ Store backups on separate disk from database files (protects against disk failure).
• ☐ Enable backup compression (reduces size, faster backups).
• ☐ Implement retention policy (delete old backups to save space).
• ☐ Verify backups regularly (RESTORE VERIFY ONLY).
• ☐ Test restores quarterly (ensure backups are usable).
• ☐ Automate with New-sqmBackupMaintenanceJob (eliminate manual processes).
• ☐ Monitor backup success/failure (SQL Agent history, alerts).
• ☐ Archive critical backups to off-site storage (disaster recovery).
• ☐ Document RTO/RPO requirements per database.
• ☐ Calculate backup storage needed (avoid running out of space).

Common Mistakes

Mistake 1: No Transaction Log Backups

-- ✗ WRONG (only full backups, data loss possible):
Full backup 9:00 PM, disaster 5:00 PM next day
→ Loss: 20 hours of data!

-- ✓ CORRECT (full + logs):
Full backup 9:00 PM, log backups every 15 min
→ Loss: Max 15 minutes (last log backup)

Mistake 2: Backups on Same Disk as Database

-- ✗ WRONG (both lost if disk fails):
Database: C:\Data\MyDatabase.mdf
Backup: C:\Backups\MyDatabase.bak
Disk C fails → Both database AND backup gone!

-- ✓ CORRECT (separate disks):
Database: C:\Data\MyDatabase.mdf (C: drive)
Backup: D:\Backups\MyDatabase.bak (D: drive)
C: fails → Database lost, but D: backup survives

Mistake 3: Never Testing Restores

-- Backup exists but is unusable:
// - Backup file corrupted
// - Backup incompatible with current version
// - Backup created during transaction (inconsistent)
// - Restore fails for unknown reason

-- Solution: Test restores regularly!
RESTORE VERIFYONLY FROM DISK = 'D:\Backup.bak';
-- Verify backup integrity

-- Actually restore to test server quarterly
// Ensures backup is usable when needed

The Bottom Line

Backups are insurance—you hope you never need them, but when disaster strikes, they're everything. The strategy is simple: know your RTO/RPO, choose your recovery model (FULL for production), implement full + differential + log backups, automate with New-sqmBackupMaintenanceJob, test restores quarterly, and store backups off-site.

Don't backup without a strategy. Don't test restores only when disaster hits. Don't store backups on the same disk as the database. Automate with sqmBackupMaintenanceJob and focus on business continuity, not backup mechanics.

One last thing: When you restore from that backup at 3 AM during a production outage, you'll understand why backups matter more than anything else in the database.