Skip to main content

31. Backup, Restore & Disaster Recovery

Level: Guided beginner

ℹ️ What You'll Learn
  • Backup types (full, differential, log)
  • Recovery models (SIMPLE, FULL, BULK-LOGGED)
  • BACKUP and RESTORE syntax
  • Point-in-time recovery
  • Backup strategy planning
  • Restore verification
  • Common backup mistakes
  • Best practices

Data loss = disaster. Backups prevent it. This article teaches recovery planning.

Why Backups Matter for Backend Developers​

No backup = lost data:

Scenario: Database corrupted at 2 PM
    v
Without backup: Data gone forever
WITH backup: Restore to 1:59 PM (minimal loss)
    v
Recovery time: Minutes (with backup) vs weeks (no backup)

Backups save businesses.

Backup Types​

Full: All data (baseline). Differential: Changes since last full backup (incremental). Transaction Log: Individual operations (point-in-time recovery).

Example 1: Full Backup​

Complete backup of database:

BACKUP DATABASE SchoolDB
TO DISK = 'C:\Backups\SchoolDB_Full.bak'
WITH 
    DESCRIPTION = 'Full backup - daily',
    COMPRESSION,  -- Compress to save space
    STATS = 10;   -- Show progress

-- Result:
Backup completed. Backed up 50000 pages (400 MB).

Full backup: Entire database. Run daily/weekly.

Example 2: Differential Backup​

Only changes since last full:

-- After full backup runs
BACKUP DATABASE SchoolDB
TO DISK = 'C:\Backups\SchoolDB_Diff.bak'
WITH 
    DIFFERENTIAL,  -- Only changes
    COMPRESSION,
    STATS = 10;

-- Result (smaller):
Backup completed. Backed up 500 pages (4 MB).

Differential: Only changed pages. Smaller, faster.

Example 3: Transaction Log Backup​

Capture each transaction:

BACKUP LOG SchoolDB
TO DISK = 'C:\Backups\SchoolDB_Log_20240524.bak'
WITH 
    COMPRESSION,
    STATS = 10;

-- Run every 15 minutes (schedule)
-- Enables point-in-time recovery

Log backup: Individual changes. Enable minute-level recovery.

Example 4: Recovery Models​

SIMPLE: No log backups. Fast, less overhead. Lose data since last full backup.

ALTER DATABASE SchoolDB SET RECOVERY SIMPLE;
-- Backup log: Not supported
-- Suitable for: Dev/test databases

FULL: Log backups. Complete recovery. Production standard.

ALTER DATABASE SchoolDB SET RECOVERY FULL;
-- Backup log: Supported
-- Suitable for: Production (critical data)

BULK-LOGGED: Log backups + bulk operations optimized.

ALTER DATABASE SchoolDB SET RECOVERY BULK_LOGGED;
-- Suitable for: Large imports with full backup

Example 5: Full Restore​

Restore database from backup:

RESTORE DATABASE SchoolDB
FROM DISK = 'C:\Backups\SchoolDB_Full.bak'
WITH 
    REPLACE,        -- Overwrite existing
    STATS = 10;     -- Show progress

-- Database restored from backup

REPLACE: Overwrites current database. BE CAREFUL.

Example 6: Point-in-Time Restore​

Restore to specific time (FULL + LOG):

-- Setup: Database in FULL recovery mode
-- 1. Run full backup (e.g., 1 AM)
-- 2. Run log backups (every 15 min)

-- Disaster at 2 PM: Data corrupted
-- Restore to 1:59 PM (safe state)

RESTORE DATABASE SchoolDB
FROM DISK = 'C:\Backups\SchoolDB_Full.bak'
WITH NORECOVERY;  -- Don't finish yet

RESTORE LOG SchoolDB
FROM DISK = 'C:\Backups\SchoolDB_Log_20240524.bak'
WITH STOPAT = '2024-05-24 13:59:00', RECOVERY;
-- Recovers at exact time

-- Result: Database at 1:59 PM (before corruption)

FULL recovery model + log backups = minute-level recovery.

Example 7: Backup Strategy​

Enterprise backup plan:

Daily Schedule:
- 1 AM: Full backup (Sunday-Saturday)
- 4 AM, 7 AM, 10 AM, 1 PM, 4 PM, 7 PM, 10 PM: Differential backups
- Every 15 minutes: Transaction log backups

SIMPLE Recovery (dev):
- Daily full backup only

FULL Recovery (production):
- Weekly full backup
- Nightly differential
- Hourly log backups

Full: Granular recovery. Differential: Faster backups than full. Logs: Minute-level recovery.

Example 8: Backup Verification​

Verify backup integrity:

-- Check backup info
RESTORE FILELISTONLY
FROM DISK = 'C:\Backups\SchoolDB_Full.bak';
-- Lists files in backup

-- Verify backup is good
RESTORE VERIFYONLY
FROM DISK = 'C:\Backups\SchoolDB_Full.bak';
-- Result: Backup set is valid

Verify before relying on backup.

Common Backup Mistakes​

Mistake 1: Backups never tested

Wrong:

-- Weekly backups, never restored
-- 2 years later, need to restore
-- Backup corrupted (discovered during restore)

Result: Can't restore when needed.

Fix: Test monthly:

-- Restore to test database monthly
RESTORE DATABASE SchoolDB_Test FROM DISK = '...';
-- Verify data integrity

Mistake 2: Storing backups on same server

Wrong:

-- C:\Backups is on database server
-- Server dies: Backups also lost

Result: Backup doesn't help (co-located).

Fix: Store remotely:

BACKUP DATABASE SchoolDB
TO DISK = '\\NetworkShare\Backups\SchoolDB.bak';
-- Remote location, separate from server

Mistake 3: No log backups (SIMPLE recovery)

Wrong:

ALTER DATABASE SchoolDB SET RECOVERY SIMPLE;
-- Production database
-- Noon: Corruption detected
-- Only restore to midnight (12+ hours lost)

Fix: Use FULL recovery:

ALTER DATABASE SchoolDB SET RECOVERY FULL;
-- Hourly log backups
-- Can restore to specific time (minutes lost, not hours)

Mistake 4: Backups stored unencrypted

Wrong:

BACKUP DATABASE SchoolDB TO DISK = 'C:\Backups\SchoolDB.bak';
-- Unencrypted file on network share
-- Anyone can copy/access sensitive data

Fix: Encrypt backup:

BACKUP DATABASE SchoolDB
TO DISK = 'C:\Backups\SchoolDB.bak'
WITH 
    ENCRYPTION (
        ALGORITHM = AES_256,
        SERVER CERTIFICATE = BackupCert
    );

Mistake 5: No backup retention policy

Wrong:

-- Backups accumulate: 10 years of files
-- Storage costs skyrocket
-- No cleanup schedule

Fix: Archive old backups:

-- Keep: 30 days daily backups
-- Archive: Older backups to cold storage
-- Delete: Backups > 1 year old

Best Practices for Backup/Restore​

  1. FULL recovery for production -- Minute-level recovery
  2. Test restores monthly -- Verify backup works
  3. Store backups remotely -- Separate from server
  4. Encrypt sensitive backups -- Protect data
  5. Hourly log backups (production) -- Minimize loss
  6. Document recovery plan -- RTO/RPO targets
  7. Backup transaction logs -- Enable point-in-time
  8. Retention policy -- Cleanup old backups
🎯 Q1: What's the difference between FULL and SIMPLE recovery?

FULL:

  • Transaction log backups supported
  • Point-in-time recovery
  • Minimal data loss
  • Production standard

SIMPLE:

  • No log backups
  • Restore to last full backup only
  • Hours/days of data loss possible
  • Dev/test databases

Production = FULL. Test = SIMPLE.

🎯 Q2: Why backup transaction logs?

Enable point-in-time recovery:

-- Full backup: 1 AM
-- Logs: Every 15 minutes
-- Disaster: 2 PM
-- Restore: To 1:59 PM (before disaster)

Logs capture every operation. Restore to exact moment.

🎯 Q3: Should backups be stored on same server?

No:

Server dies -> Backups also lost
Network share -> Separate, protected

Remote storage: Protects against hardware failure.

🎯 Q4: How do I restore to specific time?
RESTORE DATABASE SchoolDB FROM ... WITH NORECOVERY;
RESTORE LOG SchoolDB FROM ... WITH STOPAT = '2024-05-24 14:00:00', RECOVERY;

NORECOVERY: Don't finish yet. Apply logs to specific time. RECOVERY: Finish.

🎯 Q5: How often should I backup?

FULL: Weekly (Sunday night) DIFFERENTIAL: Nightly LOGS: Every hour (or 15 min for critical)

Balance: Recovery time vs storage.

🎯 Q6: Should I encrypt backups?

Yes, if sensitive data:

WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = Cert);

Protects backup file. Required for compliance.

🤖Use AI to Learn Faster
⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on Backup and restore in SQL Server. Try these prompts:

  • "Show full backup syntax with compression"
  • "What's point-in-time recovery? How do I restore to specific time?"
  • "Explain FULL vs SIMPLE recovery models"
  • "How do I verify backup integrity before restoring?"
  • "Quiz me: ask 5 questions about backups, recovery models, disaster recovery"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article​

32. Monitoring & Performance Analysis -- Finding Bottlenecks ->

nexcoding.in
nexcoding.in  ·  Free .NET Tutorials  ·  Sahasra Technologies  ·  info@nexcoding.in  ·  +91-9951510727