← Back to Articles 📚 DEVLADBLOG
📁 Sql

SQL Server Backup and Recovery: Strategies for Business Continuity

📅 2025-12-08 ✍️ Vladimir Luis ⏱️ 15-19 min

SQL Server Backup and Recovery: Strategies for Business Continuity

Introduction

[Explain backup as insurance; recovery objectives (RPO, RTO) guide strategy; testing validates recoverability.]

Prerequisites

  • SQL Server 2019+ (Standard or Enterprise)
  • Administrative access
  • Storage for backup files

Backup Types Overview

Type Scope Use Case
Full Entire database Baseline for restore chain
Differential Changes since last full Faster recovery than full + log
Transaction Log Committed transactions Point-in-time recovery
File/Filegroup Subset of database Very large databases
Copy-Only Independent backup Ad-hoc copy without breaking chain

Step-by-Step Guide

Step 1: Full Backup

BACKUP DATABASE [MyDatabase]
TO DISK = 'C:\Backups\MyDatabase_Full.bak'
WITH COMPRESSION, CHECKSUM, STATS = 10;

Step 2: Differential Backup

BACKUP DATABASE [MyDatabase]
TO DISK = 'C:\Backups\MyDatabase_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;

Step 3: Transaction Log Backup

BACKUP LOG [MyDatabase]
TO DISK = 'C:\Backups\MyDatabase_Log.trn'
WITH COMPRESSION, CHECKSUM;

Step 4: Automated Backup Schedule

SQL Server Agent Job:

-- Weekly full backup (Sunday 2 AM)
EXEC sp_add_schedule
    @schedule_name = 'WeeklyFullBackup',
    @freq_type = 8, -- Weekly
    @freq_interval = 1, -- Sunday
    @active_start_time = 020000;

-- Daily differential backup (Mon-Sat 2 AM)
EXEC sp_add_schedule
    @schedule_name = 'DailyDifferentialBackup',
    @freq_type = 8,
    @freq_interval = 126, -- Mon-Sat (2+4+8+16+32+64)
    @active_start_time = 020000;

-- Hourly log backup (every hour)
EXEC sp_add_schedule
    @schedule_name = 'HourlyLogBackup',
    @freq_type = 4, -- Daily
    @freq_interval = 1,
    @freq_subday_type = 8, -- Hours
    @freq_subday_interval = 1;

Step 5: Recovery Strategy

Scenario: Point-in-Time Recovery (Friday 3:45 PM)

-- Step 1: Restore full backup (Sunday)
RESTORE DATABASE [MyDatabase]
FROM DISK = 'C:\Backups\MyDatabase_Full.bak'
WITH NORECOVERY;

-- Step 2: Restore differential backup (Friday AM)
RESTORE DATABASE [MyDatabase]
FROM DISK = 'C:\Backups\MyDatabase_Diff_Friday.bak'
WITH NORECOVERY;

-- Step 3: Restore log backups up to 3:45 PM
RESTORE LOG [MyDatabase]
FROM DISK = 'C:\Backups\MyDatabase_Log_1PM.trn'
WITH NORECOVERY;

RESTORE LOG [MyDatabase]
FROM DISK = 'C:\Backups\MyDatabase_Log_2PM.trn'
WITH NORECOVERY;

RESTORE LOG [MyDatabase]
FROM DISK = 'C:\Backups\MyDatabase_Log_3PM.trn'
WITH STOPAT = '2025-12-05 15:45:00', RECOVERY;

Step 6: Backup Verification

RESTORE VERIFYONLY:

RESTORE VERIFYONLY
FROM DISK = 'C:\Backups\MyDatabase_Full.bak'
WITH CHECKSUM;

DBCC CHECKDB After Restore:

RESTORE DATABASE [MyDatabase_Test]
FROM DISK = 'C:\Backups\MyDatabase_Full.bak'
WITH MOVE 'MyDatabase' TO 'D:\Data\MyDatabase_Test.mdf',
     MOVE 'MyDatabase_log' TO 'E:\Logs\MyDatabase_Test_log.ldf';

DBCC CHECKDB ([MyDatabase_Test]) WITH NO_INFOMSGS;

Step 7: Azure Backup Integration

Backup to URL (Azure Blob Storage):

-- Create credential
CREATE CREDENTIAL [https://contoso.blob.core.windows.net/backups]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2021-06-08&ss=b&srt=sco&sp=rwdlacx&se=...';

-- Backup to Azure
BACKUP DATABASE [MyDatabase]
TO URL = 'https://contoso.blob.core.windows.net/backups/MyDatabase.bak'
WITH COMPRESSION, CHECKSUM;

Managed Backup to Azure:

EXEC msdb.managed_backup.sp_backup_config_basic
    @database_name = 'MyDatabase',
    @enable_backup = 1,
    @retention_days = 30,
    @storage_url = 'https://contoso.blob.core.windows.net/backups',
    @credential_name = 'AzureBackupCredential';

Step 8: Retention & Cleanup

Automated Cleanup Script:

DECLARE @CleanupDate DATETIME = DATEADD(DAY, -30, GETDATE());

EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @CleanupDate;

-- Delete old backup files (PowerShell)
-- Get-ChildItem "C:\Backups" -Filter "*.bak" | Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-30) } | Remove-Item

Advanced Strategies

Striped Backups (Parallel I/O)

BACKUP DATABASE [MyDatabase]
TO DISK = 'C:\Backups\MyDatabase_1.bak',
   DISK = 'D:\Backups\MyDatabase_2.bak',
   DISK = 'E:\Backups\MyDatabase_3.bak'
WITH COMPRESSION, CHECKSUM;

Partial Restore (Very Large Databases)

-- Restore primary filegroup only
RESTORE DATABASE [MyDatabase] FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\Backups\MyDatabase_Full.bak'
WITH PARTIAL, NORECOVERY;

-- Restore read-only filegroup later (piecemeal restore)
RESTORE DATABASE [MyDatabase] FILEGROUP = 'ArchiveData'
FROM DISK = 'C:\Backups\MyDatabase_Full.bak'
WITH RECOVERY;

Tail-Log Backup (Pre-Restore)

-- Before disaster recovery, capture uncommitted transactions
BACKUP LOG [MyDatabase]
TO DISK = 'C:\Backups\MyDatabase_TailLog.trn'
WITH NO_TRUNCATE, NORECOVERY;

Recovery Models

Model Log Behavior Recovery Options
Simple Auto-truncate after checkpoint Last full/diff only
Full Manual truncate via log backup Point-in-time
Bulk-Logged Minimal logging for bulk ops Point-in-time (with caveats)

Change Recovery Model:

ALTER DATABASE [MyDatabase] SET RECOVERY FULL;

Monitoring & Alerting

Check Backup Status:

SELECT
    database_name,
    MAX(backup_finish_date) AS LastBackup,
    type AS BackupType
FROM msdb.dbo.backupset
WHERE database_name = 'MyDatabase'
GROUP BY database_name, type
ORDER BY LastBackup DESC;

Alert on Missing Backups:

-- SQL Agent Alert: If no backup in 25 hours
IF NOT EXISTS (
    SELECT 1
    FROM msdb.dbo.backupset
    WHERE database_name = 'MyDatabase'
      AND type = 'D'
      AND backup_finish_date > DATEADD(HOUR, -25, GETDATE())
)
BEGIN
    EXEC msdb.dbo.sp_send_dbmail
        @recipients = 'dba@contoso.com',
        @subject = 'ALERT: Missing Full Backup',
        @body = 'No full backup detected for MyDatabase in the last 25 hours.';
END;

Disaster Recovery Testing

Quarterly DR Drill:

  1. Restore latest backup to test environment
  2. Run DBCC CHECKDB
  3. Validate sample queries
  4. Document RTO (Recovery Time Objective)
  5. Update runbook with lessons learned

Troubleshooting

Issue: Backup fails with "insufficient disk space"
Solution: Clean old backups; add compression; use Azure Blob storage

Issue: Restore shows log sequence number (LSN) mismatch
Solution: Restore full backup first; ensure differential/log backups are in correct sequence

Issue: Backup taking too long
Solution: Use striped backups; consider differential strategy; add compression

Best Practices

  • Follow 3-2-1 rule: 3 copies, 2 media types, 1 offsite
  • Test restores regularly (monthly minimum)
  • Document recovery procedures (runbook)
  • Monitor backup job failures
  • Use CHECKSUM to validate backup integrity
  • Automate retention policies

Key Takeaways

  • Full + differential + log backups enable flexible recovery.
  • Point-in-time restore requires Full recovery model.
  • Azure integration simplifies offsite storage.
  • Regular testing validates recoverability.

Next Steps

  • Implement Always On Availability Groups for HA
  • Automate backup validation with PowerShell
  • Set up cross-region backup replication

Additional Resources

Is your disaster recovery plan tested and ready?