SQL Server Backup and Recovery Strategy — RPO, RTO, Schedules, and Disaster Recovery

 1. RPO and RTO — What They Mean and How to Set Them

Before writing a single line of T-SQL, you need answers to two business questions. Every backup decision flows from them.

Recovery Point Objective (RPO) is the maximum amount of data loss your business can tolerate, measured as time. If your RPO is 15 minutes, the worst case after a failure should be that you lose no more than 15 minutes of transactions. RPO directly governs how frequently you take transaction log backups — your log backup interval equals your RPO ceiling.

Recovery Time Objective (RTO) is the maximum acceptable downtime — how long can the system be unavailable before business damage becomes unacceptable? An e-commerce site processing $50,000/hour cannot tolerate an 8-hour RTO. A nightly batch reporting database might tolerate 4 hours. RTO governs your restore infrastructure: how fast can you spin up a replacement server, locate the backup files, and run through the restore chain?

Database Criticality Tiers

A practical approach is to classify every database into one of three tiers and assign standard RPO/RTO targets to each tier:

Calculating Your Actual Data Loss Window

-- See how long it has been since the last log backup for every database
-- Run this on your monitoring server or via a linked server
SELECT
    d.name                                          AS DatabaseName,
    d.recovery_model_desc                           AS RecoveryModel,
    MAX(b.backup_finish_date)                       AS LastLogBackup,
    DATEDIFF(MINUTE, MAX(b.backup_finish_date),
             GETDATE())                             AS MinutesSinceLastLogBackup,
    CASE
        WHEN d.recovery_model_desc = 'SIMPLE'
            THEN 'Log backups not applicable (SIMPLE)'
        WHEN MAX(b.backup_finish_date) IS NULL
            THEN 'NEVER BACKED UP — CRITICAL RISK'
        WHEN DATEDIFF(MINUTE, MAX(b.backup_finish_date), GETDATE()) > 60
            THEN 'WARNING: Last backup > 1 hour ago'
        ELSE 'OK'
    END                                             AS BackupStatus
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b
    ON b.database_name = d.name
    AND b.type = 'L'               -- 'L' = Log, 'D' = Database (full), 'I' = Differential
GROUP BY d.name, d.recovery_model_desc
WHERE d.database_id > 4            -- exclude system databases
ORDER BY MinutesSinceLastLogBackup DESC;

 2. Recovery Models — SIMPLE, FULL, and BULK_LOGGED

The recovery model of a database determines what the transaction log records, how log truncation works, and critically — whether point-in-time recovery is possible. This is the single most important setting for your backup strategy.

SIMPLE Recovery Model

SQL Server automatically truncates (recycles) the inactive portion of the transaction log after each checkpoint. This keeps the log file small, but it means:

• Transaction log backups are not possible
• Point-in-time recovery is not possible
• You can only recover to the last full or differential backup
• Maximum data loss equals the interval between your full/differential backups

SIMPLE is appropriate for development databases, test databases, and any database where losing a full day of data is acceptable.

FULL Recovery Model

Every transaction is fully logged and the log is retained until a log backup truncates it. This enables:

• Transaction log backups — captured every N minutes
• Point-in-time recovery to any moment covered by the log chain
• Zero data loss recovery when combined with frequent log backups

FULL is mandatory for any mission-critical or business-critical database. The downside is log file growth — you must take regular log backups or the log file will grow until disk fills.

BULK_LOGGED Recovery Model

A hybrid mode that minimally logs certain bulk operations (SELECT INTO, bulk INSERT, WRITETEXT, UPDATETEXT, CREATE INDEX, ALTER INDEX REBUILD). Used temporarily during large data loads to reduce log generation, then switched back to FULL. The log backup taken while in BULK_LOGGED mode requires access to the data pages to reconstruct the bulk operations, making the backup larger and making point-in-time recovery unavailable during the bulk-logged window.

Checking and Changing the Recovery Model

-- Check recovery model for all user databases
SELECT
    name,
    recovery_model_desc,
    log_reuse_wait_desc,
    log_size_mb = (
        SELECT SUM(size) * 8.0 / 1024
        FROM sys.master_files mf
        WHERE mf.database_id = d.database_id
          AND mf.type_desc = 'LOG'
    )
FROM sys.databases d
WHERE database_id > 4
ORDER BY name;

-- Change to FULL recovery model
ALTER DATABASE YourDatabase SET RECOVERY FULL;

-- Verify the change
SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = 'YourDatabase';

-- IMPORTANT: After switching FROM SIMPLE to FULL,
-- take a full backup immediately to start the log chain.
-- Until you do, log backups cannot be taken.
BACKUP DATABASE YourDatabase
    TO DISK = 'D:\Backups\YourDatabase_postswitch.bak'
    WITH COMPRESSION, CHECKSUM, STATS = 10;

 3. Backup Types — Full, Differential, Log, Copy-Only, File

SQL Server supports several backup types. Understanding how they chain together is essential to building a correct restore strategy.

The Backup Chain Explained

A restore chain always starts with a full backup and works forward in time. Differentials are optional acceleration points — they reduce the number of log backups that must be applied. Here is how a typical week looks for a mission-critical database:

-- BACKUP CHAIN VISUALIZATION
--
-- Sunday 02:00   → FULL BACKUP  ← always the starting anchor
-- Monday 02:00   → DIFFERENTIAL (captures all changes since Sunday full)
-- Monday 08:00   → LOG backup #1 (captures Mon 02:00 → 08:00)
-- Monday 08:15   → LOG backup #2
-- Monday 08:30   → LOG backup #3
-- ...every 15 min...
-- Tuesday 02:00  → DIFFERENTIAL (captures all changes since Sunday full — larger than Monday diff)
-- Tuesday 08:00  → LOG backup (captures Tue 02:00 → 08:00)
-- ...
-- To restore to Tuesday 10:45:
--   1. Restore Sunday FULL backup         (WITH NORECOVERY)
--   2. Restore Tuesday DIFFERENTIAL       (WITH NORECOVERY)  ← skip Monday diff!
--   3. Apply each LOG backup from Tue 02:00 through 10:45   (WITH NORECOVERY each)
--   4. Final: bring database online       (WITH RECOVERY)

Concrete Backup Schedules by Tier

Mission-Critical (RPO < 5 min, RTO < 1 hour):

• Full backup: Sunday 01:00 AM
• Differential: Every 4 hours (05:00, 09:00, 13:00, 17:00, 21:00 Mon–Sat)
• Transaction log: Every 5 minutes, 24x7
• Retention: Full — 4 weeks, Diff — 2 weeks, Log — 2 weeks

Business-Critical (RPO < 30 min, RTO < 4 hours):

• Full backup: Sunday 01:00 AM
• Differential: Daily at 01:00 AM (Mon–Sat)
• Transaction log: Every 30 minutes
• Retention: Full — 4 weeks, Diff — 2 weeks, Log — 2 weeks

Non-Critical (RPO < 24 hours):

• Full backup: Sunday 02:00 AM (SIMPLE recovery model is acceptable)
• No differential, no log backups needed
• Retention: Full — 4 weeks

 4. Writing Robust Backup Scripts

Ad-hoc backup commands are fragile. Production backup jobs need error handling, checksums, compression, progress reporting, and built-in verification. Here are production-grade T-SQL examples for each backup type.

Full Database Backup with All Best-Practice Options

-- Full backup with COMPRESSION, CHECKSUM, STATS, and DESCRIPTION
BACKUP DATABASE [YourDatabase]
TO DISK = N'D:\Backups\YourDatabase\YourDatabase_FULL_20260611_020000.bak'
WITH
    COMPRESSION,                          -- CPU trade for smaller backup file
    CHECKSUM,                             -- Verify each page; written to backup header
    STATS = 10,                           -- Print progress every 10%
    DESCRIPTION = N'Weekly full backup',
    NAME = N'YourDatabase-Full-20260611';

-- Immediately verify the backup after writing
RESTORE VERIFYONLY
FROM DISK = N'D:\Backups\YourDatabase\YourDatabase_FULL_20260611_020000.bak'
WITH CHECKSUM;
GO

Differential Backup

BACKUP DATABASE [YourDatabase]
TO DISK = N'D:\Backups\YourDatabase\YourDatabase_DIFF_20260611_090000.bak'
WITH
    DIFFERENTIAL,
    COMPRESSION,
    CHECKSUM,
    STATS = 10,
    DESCRIPTION = N'Differential backup 09:00',
    NAME = N'YourDatabase-Diff-20260611-0900';
GO

Transaction Log Backup

BACKUP LOG [YourDatabase]
TO DISK = N'D:\Backups\YourDatabase\Logs\YourDatabase_LOG_20260611_093000.bak'
WITH
    COMPRESSION,
    CHECKSUM,
    STATS = 10,
    NAME = N'YourDatabase-Log-20260611-0930';
GO

Copy-Only Full Backup (pre-deployment snapshot)

-- Use before a major deployment — does NOT reset the differential base
BACKUP DATABASE [YourDatabase]
TO DISK = N'D:\Backups\YourDatabase\YourDatabase_COPYONLY_20260611_predeployment.bak'
WITH
    COPY_ONLY,
    COMPRESSION,
    CHECKSUM,
    DESCRIPTION = N'Pre-deployment copy-only backup',
    NAME = N'YourDatabase-CopyOnly-20260611';
GO

Multi-Stripe Backup for Large Databases

For very large databases, stripe the backup across multiple files to increase write throughput. SQL Server writes to all stripes in parallel.

-- Striped backup across 4 files — SQL Server writes to all 4 in parallel
-- Each file is approximately 1/4 of the total backup size
BACKUP DATABASE [VeryLargeDatabase]
TO
    DISK = N'E:\Backups\stripe1\VLDb_FULL_20260611_01.bak',
    DISK = N'F:\Backups\stripe2\VLDb_FULL_20260611_02.bak',
    DISK = N'G:\Backups\stripe3\VLDb_FULL_20260611_03.bak',
    DISK = N'H:\Backups\stripe4\VLDb_FULL_20260611_04.bak'
WITH
    COMPRESSION,
    CHECKSUM,
    STATS = 5,
    FORMAT,                               -- WITH FORMAT: initializes all files as a new media set
    MEDIANAME = N'VLDbStripes',
    NAME = N'VeryLargeDatabase-Full-Striped-20260611';
GO

-- To verify a striped backup, reference ALL stripe files
RESTORE VERIFYONLY
FROM
    DISK = N'E:\Backups\stripe1\VLDb_FULL_20260611_01.bak',
    DISK = N'F:\Backups\stripe2\VLDb_FULL_20260611_02.bak',
    DISK = N'G:\Backups\stripe3\VLDb_FULL_20260611_03.bak',
    DISK = N'H:\Backups\stripe4\VLDb_FULL_20260611_04.bak'
WITH CHECKSUM;
GO

Reading Backup History and File Headers

-- Inspect backup header — shows all backup sets in a file
RESTORE HEADERONLY
FROM DISK = N'D:\Backups\YourDatabase\YourDatabase_FULL_20260611_020000.bak';
-- Returns: BackupName, BackupType, ExpirationDate, Compressed, DatabaseName,
--          ServerName, UserName, DatabaseVersion, FirstLSN, LastLSN, etc.

-- Inspect file list — what data/log files are inside the backup
RESTORE FILELISTONLY
FROM DISK = N'D:\Backups\YourDatabase\YourDatabase_FULL_20260611_020000.bak';
-- Returns: LogicalName, PhysicalName, Type (D=data, L=log), FileGroupName, Size, etc.

-- Check backup history from msdb for last 7 days
SELECT TOP 50
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS duration_sec,
    CAST(bs.backup_size / 1048576.0 AS DECIMAL(18,1))             AS backup_size_mb,
    CAST(bs.compressed_backup_size / 1048576.0 AS DECIMAL(18,1))  AS compressed_mb,
    CAST(100.0 - (bs.compressed_backup_size * 100.0 / bs.backup_size) AS DECIMAL(5,1)) AS pct_saved,
    CASE bs.type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Log'
        WHEN 'F' THEN 'File'
        ELSE bs.type
    END AS backup_type,
    bmf.physical_device_name
FROM msdb.dbo.backupset        bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = 'YourDatabase'
  AND bs.backup_start_date >= DATEADD(DAY, -7, GETDATE())
ORDER BY bs.backup_start_date DESC;

Complete Dynamic Backup Maintenance Job Script

This script dynamically builds backup paths using the current date/time and can be dropped into a SQL Server Agent job step:

-- =====================================================================
-- Production Backup Job Script
-- Designed for SQL Server Agent job step (T-SQL)
-- Replace @BackupRoot, @DatabaseName, and @BackupType as needed
-- =====================================================================
DECLARE @DatabaseName  SYSNAME       = N'YourDatabase';
DECLARE @BackupType    CHAR(1)       = 'D';   -- 'D'=Full, 'I'=Diff, 'L'=Log
DECLARE @BackupRoot    NVARCHAR(500) = N'D:\Backups';
DECLARE @FileName      NVARCHAR(1000);
DECLARE @BackupCmd     NVARCHAR(MAX);
DECLARE @Timestamp     NVARCHAR(20)  = CONVERT(NVARCHAR(20), GETDATE(), 112)
                                       + '_'
                                       + REPLACE(CONVERT(NVARCHAR(8), GETDATE(), 108), ':', '');
DECLARE @SubDir        NVARCHAR(20);
DECLARE @TypeName      NVARCHAR(20);

-- Map type to subfolder and name
SELECT
    @SubDir   = CASE @BackupType WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Diff' WHEN 'L' THEN 'Log' END,
    @TypeName = CASE @BackupType WHEN 'D' THEN 'FULL' WHEN 'I' THEN 'DIFF' WHEN 'L' THEN 'LOG' END;

SET @FileName = @BackupRoot + N'\' + @DatabaseName + N'\' + @SubDir
              + N'\' + @DatabaseName + '_' + @TypeName + '_' + @Timestamp + N'.bak';

-- Build and execute the BACKUP command
SET @BackupCmd = CASE @BackupType
    WHEN 'D' THEN
        N'BACKUP DATABASE [' + @DatabaseName + N']
          TO DISK = N''' + @FileName + N'''
          WITH COMPRESSION, CHECKSUM, STATS = 10,
               NAME = N''' + @DatabaseName + '-' + @TypeName + '-' + @Timestamp + N''';'
    WHEN 'I' THEN
        N'BACKUP DATABASE [' + @DatabaseName + N']
          TO DISK = N''' + @FileName + N'''
          WITH DIFFERENTIAL, COMPRESSION, CHECKSUM, STATS = 10,
               NAME = N''' + @DatabaseName + '-' + @TypeName + '-' + @Timestamp + N''';'
    WHEN 'L' THEN
        N'BACKUP LOG [' + @DatabaseName + N']
          TO DISK = N''' + @FileName + N'''
          WITH COMPRESSION, CHECKSUM, STATS = 10,
               NAME = N''' + @DatabaseName + '-' + @TypeName + '-' + @Timestamp + N''';'
    END;

PRINT @BackupCmd;
EXEC sp_executesql @BackupCmd;

-- Verify immediately after backup
DECLARE @VerifyCmd NVARCHAR(MAX) = N'RESTORE VERIFYONLY FROM DISK = N'''
    + @FileName + N''' WITH CHECKSUM;';
EXEC sp_executesql @VerifyCmd;

PRINT 'Backup and verification complete: ' + @FileName;

 5. Restore Procedures — Full, Differential, Log Chain, Point-in-Time

Backups are worthless if you do not know how to restore from them. Practice these restore commands in a test environment before you need them in production at 2 AM.

Restore Full Backup Only

-- Restore a full backup and bring the database online immediately
-- Use this when you just need the database back to the full backup state
RESTORE DATABASE [YourDatabase_Restored]
FROM DISK = N'D:\Backups\YourDatabase\Full\YourDatabase_FULL_20260608_020000.bak'
WITH
    MOVE N'YourDatabase'      TO N'E:\Data\YourDatabase_Restored.mdf',
    MOVE N'YourDatabase_log'  TO N'F:\Logs\YourDatabase_Restored.ldf',
    RECOVERY,                         -- WITH RECOVERY: brings DB online, no more logs to apply
    REPLACE,                          -- Overwrite if database name already exists
    STATS = 10;
GO

Restore Full + Differential + Log Chain

-- Step 1: Restore the full backup (WITH NORECOVERY leaves DB in restoring state)
RESTORE DATABASE [YourDatabase]
FROM DISK = N'D:\Backups\YourDatabase\Full\YourDatabase_FULL_20260608_020000.bak'
WITH
    NORECOVERY,                       -- IMPORTANT: do NOT bring online yet
    MOVE N'YourDatabase'      TO N'E:\Data\YourDatabase.mdf',
    MOVE N'YourDatabase_log'  TO N'F:\Logs\YourDatabase.ldf',
    REPLACE,
    STATS = 10;
GO

-- Step 2: Apply the most recent differential backup (WITH NORECOVERY)
-- Note: you only need the LAST differential taken after the full backup
RESTORE DATABASE [YourDatabase]
FROM DISK = N'D:\Backups\YourDatabase\Diff\YourDatabase_DIFF_20260611_010000.bak'
WITH NORECOVERY, STATS = 10;
GO

-- Step 3: Apply each transaction log backup in order (WITH NORECOVERY for all except last)
RESTORE LOG [YourDatabase]
FROM DISK = N'D:\Backups\YourDatabase\Log\YourDatabase_LOG_20260611_013000.bak'
WITH NORECOVERY, STATS = 10;
GO

RESTORE LOG [YourDatabase]
FROM DISK = N'D:\Backups\YourDatabase\Log\YourDatabase_LOG_20260611_014500.bak'
WITH NORECOVERY, STATS = 10;
GO

-- Step 4: Apply the final log backup and bring the database online
RESTORE LOG [YourDatabase]
FROM DISK = N'D:\Backups\YourDatabase\Log\YourDatabase_LOG_20260611_020000.bak'
WITH RECOVERY;                        -- WITH RECOVERY: rolls back incomplete transactions, brings DB online
GO

PRINT 'Database restored and online.';

Point-in-Time Recovery with STOPAT

This is the most powerful recovery scenario — restore the database to the exact moment just before a disaster (accidental DELETE, DROP TABLE, runaway UPDATE).

-- Scenario: Someone ran DROP TABLE Orders at 10:47 AM on June 11, 2026.
-- Goal: restore to 10:46:50 AM — just before the drop.

-- Step 1: Take a tail-log backup FIRST (captures log from last backup to now)
-- This is critical — do this before restoring anything!
BACKUP LOG [YourDatabase]
TO DISK = N'D:\Backups\YourDatabase\Log\YourDatabase_LOG_TAILLOG.bak'
WITH NORECOVERY,    -- Takes DB offline — necessary to prevent more changes
     COMPRESSION, CHECKSUM, NO_TRUNCATE,
     DESCRIPTION = N'Tail-log backup before point-in-time restore';
GO

-- Step 2: Restore the full backup
RESTORE DATABASE [YourDatabase]
FROM DISK = N'D:\Backups\YourDatabase\Full\YourDatabase_FULL_20260608_020000.bak'
WITH NORECOVERY, REPLACE, STATS = 10;
GO

-- Step 3: Restore the most recent differential
RESTORE DATABASE [YourDatabase]
FROM DISK = N'D:\Backups\YourDatabase\Diff\YourDatabase_DIFF_20260611_010000.bak'
WITH NORECOVERY, STATS = 10;
GO

-- Step 4: Restore each log backup in sequence up to (but not including) the disaster
-- Apply all logs with NORECOVERY until you reach the tail-log backup
RESTORE LOG [YourDatabase]
FROM DISK = N'D:\Backups\YourDatabase\Log\YourDatabase_LOG_20260611_013000.bak'
WITH NORECOVERY;
GO

RESTORE LOG [YourDatabase]
FROM DISK = N'D:\Backups\YourDatabase\Log\YourDatabase_LOG_20260611_020000.bak'
WITH NORECOVERY;
GO

-- Continue applying all intermediate log files...
-- (omitted for brevity — apply all log files between diff and tail-log)

-- Step 5: Apply the tail-log backup WITH STOPAT — one second before the DROP
RESTORE LOG [YourDatabase]
FROM DISK = N'D:\Backups\YourDatabase\Log\YourDatabase_LOG_TAILLOG.bak'
WITH
    RECOVERY,
    STOPAT = '2026-06-11T10:46:50';   -- ISO 8601 format: YYYY-MM-DDTHH:MM:SS
GO

-- The Orders table is back.
-- Verify:
SELECT TOP 10 * FROM YourDatabase.dbo.Orders;

 6. Testing Restores — Untested Backups Are Not Backups

The most dangerous phrase in database administration is "we have backups." The question is: have you tested restoring them recently? Backup files can be corrupt. Drives can develop silent errors. VERIFYONLY checks the structure but does not guarantee a successful restore. The only proof is an actual restore.

Automated Weekly Restore Verification Procedure

-- =====================================================================
-- Weekly Restore Verification Stored Procedure
-- Restores latest full backup of each critical database to a test instance
-- Runs DBCC CHECKDB and row-count spot checks, logs results
-- =====================================================================
CREATE OR ALTER PROCEDURE dbo.usp_WeeklyRestoreVerification
    @DatabaseName   SYSNAME,
    @RestoreTarget  SYSNAME = N'TestServer\SQLTEST',   -- test instance
    @BackupRoot     NVARCHAR(500) = N'D:\Backups'
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @LatestFull     NVARCHAR(1000);
    DECLARE @RestoreCmd     NVARCHAR(MAX);
    DECLARE @CheckCmd       NVARCHAR(MAX);
    DECLARE @TestDbName     SYSNAME = @DatabaseName + N'_RestoreTest';
    DECLARE @LogMsg         NVARCHAR(MAX);

    -- 1. Find the most recent successful full backup
    SELECT TOP 1
        @LatestFull = bmf.physical_device_name
    FROM msdb.dbo.backupset        bs
    JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
    WHERE bs.database_name = @DatabaseName
      AND bs.type = 'D'
      AND bs.is_damaged = 0
    ORDER BY bs.backup_finish_date DESC;

    IF @LatestFull IS NULL
    BEGIN
        RAISERROR('No valid full backup found for database %s', 16, 1, @DatabaseName);
        RETURN;
    END

    SET @LogMsg = 'Starting restore test for ' + @DatabaseName
                  + ' from ' + @LatestFull + ' at ' + CONVERT(NVARCHAR, GETDATE(), 120);
    PRINT @LogMsg;

    -- 2. Restore to test database (REPLACE drops existing restore-test DB)
    SET @RestoreCmd = N'
    RESTORE DATABASE [' + @TestDbName + N']
    FROM DISK = N''' + @LatestFull + N'''
    WITH
        REPLACE,
        RECOVERY,
        STATS = 20,
        MOVE N''' + @DatabaseName + N''' TO N''D:\RestoreTest\' + @TestDbName + N'.mdf'',
        MOVE N''' + @DatabaseName + N'_log'' TO N''D:\RestoreTest\' + @TestDbName + N'.ldf'';';

    EXEC sp_executesql @RestoreCmd;
    PRINT 'Restore complete. Running DBCC CHECKDB...';

    -- 3. Run DBCC CHECKDB — detects allocation, page, and row errors
    SET @CheckCmd = N'DBCC CHECKDB ([' + @TestDbName + N'])
                      WITH NO_INFOMSGS, ALL_ERRORMSGS;';
    EXEC sp_executesql @CheckCmd;
    PRINT 'DBCC CHECKDB passed.';

    -- 4. Row count spot check on key tables
    -- (Customize the table list for each database)
    SET @CheckCmd = N'
    SELECT ''Table'' AS ObjectType, t.name AS TableName,
           p.rows AS ApproxRowCount
    FROM [' + @TestDbName + N'].sys.tables t
    JOIN [' + @TestDbName + N'].sys.partitions p
         ON p.object_id = t.object_id AND p.index_id IN (0,1)
    WHERE p.rows > 0
    ORDER BY p.rows DESC;';
    EXEC sp_executesql @CheckCmd;

    -- 5. Drop the test database (clean up)
    SET @CheckCmd = N'DROP DATABASE IF EXISTS [' + @TestDbName + N'];';
    EXEC sp_executesql @CheckCmd;

    -- 6. Log success to msdb backup_restore_history (via custom table or event log)
    PRINT 'Restore verification PASSED for ' + @DatabaseName
          + ' at ' + CONVERT(NVARCHAR, GETDATE(), 120);
END;
GO

-- Schedule this as a SQL Server Agent job, weekly on Saturday night:
-- EXEC dbo.usp_WeeklyRestoreVerification @DatabaseName = 'YourDatabase';

What to Check After a Restore

• DBCC CHECKDB — runs consistency checks on all pages, indexes, and allocation structures. No errors = clean database.
• Row counts on key tables — compare against production counts. A backup that restores successfully but has 0 rows in the Orders table signals a backup that was taken while a truncation was in progress.
• Login verification — logins exist at the server level but database users are at the database level. After restoring to a different server, fix orphaned users with ALTER USER ... WITH LOGIN = ... or sp_change_users_login.
• Application smoke test — if possible, point a read-only copy of the application at the restored database and verify key workflows return correct data.

-- Fix orphaned database users after restoring to a different server
-- Find orphaned users (SID in db does not match any server login SID)
SELECT dp.name AS DatabaseUser, dp.sid AS DatabaseSID,
       sp.sid AS ServerLoginSID
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S', 'U')       -- SQL and Windows users
  AND dp.principal_id > 4         -- exclude built-in users
  AND sp.sid IS NULL;             -- NULL = no matching login found

-- Fix orphaned user by re-mapping to a login
ALTER USER [AppServiceUser] WITH LOGIN = [AppServiceUser];

-- Or use the legacy stored proc
EXEC sp_change_users_login 'Update_One', 'AppServiceUser', 'AppServiceUser';

 7. Ola Hallengren's Maintenance Solution

Writing and maintaining your own backup stored procedures is possible but time-consuming. Ola Hallengren's SQL Server Maintenance Solution (ola.hallengren.com) is the industry-standard free solution used by thousands of production SQL Server instances worldwide. It provides:

• DatabaseBackup — backup all or selected databases with every option (compression, checksum, encryption, cloud, cleanup)
• DatabaseIntegrityCheck — wrapper around DBCC CHECKDB with intelligent scheduling
• IndexOptimize — rebuilds/reorganizes indexes based on fragmentation thresholds
• CommandLog table — every operation logged with duration, rows affected, and error details

Installation

-- 1. Download MaintenanceSolution.sql from https://ola.hallengren.com
-- 2. Run the script against your target database (usually master or DBAtools)
-- The script creates: stored procedures, CommandLog table, and SQL Agent jobs

-- Verify installation
SELECT name, create_date
FROM sys.objects
WHERE name IN ('DatabaseBackup', 'DatabaseIntegrityCheck', 'IndexOptimize', 'CommandExecute')
ORDER BY name;

Running Backups with Ola's Solution

-- Full backup of all user databases with compression, checksum, 7-day cleanup
EXEC dbo.DatabaseBackup
    @Databases          = 'USER_DATABASES',
    @Directory          = N'D:\Backups',
    @BackupType         = 'FULL',
    @Compress           = 'Y',
    @CheckSum           = 'Y',
    @Verify             = 'Y',
    @CleanupTime        = 168,           -- 168 hours = 7 days
    @LogToTable         = 'Y',
    @Execute            = 'Y';

-- Transaction log backup every 15 minutes for all FULL-model databases
EXEC dbo.DatabaseBackup
    @Databases          = 'USER_DATABASES',
    @Directory          = N'D:\Backups',
    @BackupType         = 'LOG',
    @Compress           = 'Y',
    @CheckSum           = 'Y',
    @Verify             = 'Y',
    @CleanupTime        = 336,           -- 14 days for log files
    @LogToTable         = 'Y',
    @Execute            = 'Y';

-- Differential backup for specific critical databases only
EXEC dbo.DatabaseBackup
    @Databases          = 'CriticalDB1,CriticalDB2',
    @Directory          = N'D:\Backups',
    @BackupType         = 'DIFF',
    @Compress           = 'Y',
    @CheckSum           = 'Y',
    @Verify             = 'Y',
    @CleanupTime        = 336,
    @LogToTable         = 'Y',
    @Execute            = 'Y';

Recommended Agent Job Schedule

Checking the CommandLog

-- View recent backup job outcomes from Ola's CommandLog table
SELECT TOP 50
    DatabaseName,
    SchemaName,
    ObjectName,
    CommandType,
    Command,
    StartTime,
    EndTime,
    DATEDIFF(SECOND, StartTime, EndTime) AS DurationSeconds,
    ErrorNumber,
    ErrorMessage
FROM dbo.CommandLog
WHERE CommandType LIKE 'BACKUP%'
  AND StartTime >= DATEADD(DAY, -1, GETDATE())
ORDER BY StartTime DESC;

-- Find any jobs that ended with errors
SELECT *
FROM dbo.CommandLog
WHERE ErrorNumber <> 0
  AND StartTime >= DATEADD(DAY, -7, GETDATE())
ORDER BY StartTime DESC;

 8. Backup to Azure Blob Storage

Backing up to Azure Blob Storage (URL backup) provides off-site protection, geo-redundancy, and eliminates tape management. For Azure-hosted SQL Servers or hybrid environments, it is often the preferred or mandatory backup target.

Setting Up the Credential

-- Step 1: Create a Shared Access Signature (SAS) in Azure Portal
-- Azure Portal → Storage Account → Containers → your-backup-container
-- → Shared access signature → generate SAS with: Read, Write, Delete, List
-- Copy the SAS token (starts with ?sv=...)

-- Step 2: Create a SQL Server credential for the Azure container
CREATE CREDENTIAL [https://yourstorage.blob.core.windows.net/sqlbackups]
WITH
    IDENTITY    = N'SHARED ACCESS SIGNATURE',
    SECRET      = N'sv=2021-08-06&ss=b&srt=sco&sp=rwdlacupiytfx&se=2027-01-01T00:00:00Z&st=2026-01-01T00:00:00Z&spr=https&sig=YourSignatureHere';
-- Note: the SECRET is the SAS token WITHOUT the leading '?'

-- Verify credential was created
SELECT name, credential_identity
FROM sys.credentials
WHERE name LIKE 'https://%';

Backing Up to Azure Blob Storage

-- Single-file backup to Azure Blob
BACKUP DATABASE [YourDatabase]
TO URL = N'https://yourstorage.blob.core.windows.net/sqlbackups/YourDatabase/YourDatabase_FULL_20260611.bak'
WITH
    COMPRESSION,
    CHECKSUM,
    STATS = 10,
    NAME = N'YourDatabase-Full-AzureBlob-20260611';
GO

-- Striped backup to multiple blobs in parallel (for large databases)
-- Azure supports up to 64 stripes
BACKUP DATABASE [VeryLargeDatabase]
TO
    URL = N'https://yourstorage.blob.core.windows.net/sqlbackups/VLDb/VLDb_FULL_20260611_01.bak',
    URL = N'https://yourstorage.blob.core.windows.net/sqlbackups/VLDb/VLDb_FULL_20260611_02.bak',
    URL = N'https://yourstorage.blob.core.windows.net/sqlbackups/VLDb/VLDb_FULL_20260611_03.bak',
    URL = N'https://yourstorage.blob.core.windows.net/sqlbackups/VLDb/VLDb_FULL_20260611_04.bak'
WITH COMPRESSION, CHECKSUM, STATS = 5;
GO

-- Transaction log backup to Azure
BACKUP LOG [YourDatabase]
TO URL = N'https://yourstorage.blob.core.windows.net/sqlbackups/YourDatabase/Logs/YourDatabase_LOG_20260611_093000.bak'
WITH COMPRESSION, CHECKSUM, STATS = 10;
GO

-- Verify backup from Azure
RESTORE VERIFYONLY
FROM URL = N'https://yourstorage.blob.core.windows.net/sqlbackups/YourDatabase/YourDatabase_FULL_20260611.bak'
WITH CHECKSUM;

Azure Backup — Pros and Cons

 9. Disaster Recovery Scenarios

Theory is useless without practice. Here are step-by-step playbooks for the most common disaster scenarios.

Scenario A: Complete Server Loss

The database server hardware fails catastrophically. No data is accessible from the original server.

-- STEP 1: Provision a new SQL Server (same version or higher)
-- Install SQL Server on the new hardware
-- Ensure the SQL Server version matches or exceeds the backup's compatibility level

-- STEP 2: Copy backup files from remote storage / Azure Blob / tape to new server

-- STEP 3: Restore the system databases FIRST (if applicable)
-- Restoring master requires starting SQL Server in single-user mode
-- This restores logins, linked servers, server-level configurations

-- For user databases, restore each one:
RESTORE DATABASE [YourDatabase]
FROM DISK = N'D:\Backups\YourDatabase_FULL_latest.bak'
WITH
    NORECOVERY,
    MOVE N'YourDatabase'     TO N'E:\Data\YourDatabase.mdf',
    MOVE N'YourDatabase_log' TO N'F:\Logs\YourDatabase.ldf',
    REPLACE, STATS = 10;

-- Apply differential (if available)
RESTORE DATABASE [YourDatabase]
FROM DISK = N'D:\Backups\YourDatabase_DIFF_latest.bak'
WITH NORECOVERY, STATS = 10;

-- Apply all log backups in order
RESTORE LOG [YourDatabase]
FROM DISK = N'D:\Backups\Logs\YourDatabase_LOG_001.bak'
WITH NORECOVERY;
-- ... repeat for all log files ...

-- Bring online
RESTORE DATABASE [YourDatabase] WITH RECOVERY;

-- STEP 4: Fix orphaned users (logins may not exist on new server)
USE [YourDatabase];
EXEC sp_change_users_login 'Report';        -- find orphans
ALTER USER [AppUser] WITH LOGIN = [AppUser]; -- remap each

-- STEP 5: Update connection strings to point to new server
-- STEP 6: Verify application connectivity and run smoke tests

Scenario B: Single Database Corruption (DBCC CHECKDB Errors)

-- Step 1: Run DBCC CHECKDB to assess damage
DBCC CHECKDB ([YourDatabase]) WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- Step 2: If errors are minor (non-data-loss), try REPAIR_REBUILD
-- WARNING: This may remove data. Only use as last resort if you have no backup!
ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB ([YourDatabase], REPAIR_REBUILD) WITH NO_INFOMSGS;
ALTER DATABASE [YourDatabase] SET MULTI_USER;

-- Step 3: If backup-based restore is needed, take a tail-log backup first
BACKUP LOG [YourDatabase]
TO DISK = N'D:\Backups\YourDatabase_TAILLOG_recovery.bak'
WITH NO_TRUNCATE, NORECOVERY, COMPRESSION, CHECKSUM;

-- Step 4: Restore from last known-good backup (see Section 5 for full restore chain)

Scenario C: Accidental Table Drop — Point-in-Time Recovery

-- Developer ran: DROP TABLE dbo.Orders at 2026-06-11 10:47:03
-- Recovery target: 2026-06-11 10:47:00 (3 seconds before the drop)

-- Step 1: Immediately take a tail-log backup (captures current log)
BACKUP LOG [YourDatabase]
TO DISK = N'D:\Backups\YourDatabase_TAILLOG_20260611.bak'
WITH NORECOVERY, NO_TRUNCATE, COMPRESSION, CHECKSUM;
-- Note: This takes the DB offline (NORECOVERY). That is intentional.

-- Step 2: Restore to a parallel database (do NOT overwrite production until verified!)
RESTORE DATABASE [YourDatabase_Recovery]
FROM DISK = N'D:\Backups\YourDatabase_FULL_20260608.bak'
WITH NORECOVERY, REPLACE,
     MOVE N'YourDatabase'     TO N'E:\Recovery\YourDatabase_Recovery.mdf',
     MOVE N'YourDatabase_log' TO N'F:\Recovery\YourDatabase_Recovery.ldf';

-- Apply differential
RESTORE DATABASE [YourDatabase_Recovery]
FROM DISK = N'D:\Backups\YourDatabase_DIFF_20260611_010000.bak'
WITH NORECOVERY;

-- Apply logs up to the tail-log backup with STOPAT
RESTORE LOG [YourDatabase_Recovery]
FROM DISK = N'D:\Backups\YourDatabase_TAILLOG_20260611.bak'
WITH RECOVERY, STOPAT = '2026-06-11T10:47:00';

-- Step 3: Extract the dropped table data from recovery DB
SELECT * INTO [YourDatabase].dbo.Orders_Recovered
FROM [YourDatabase_Recovery].dbo.Orders;

-- Step 4: Rename and verify, then bring production back online
-- EXEC sp_rename 'dbo.Orders', 'Orders_Old';
-- EXEC sp_rename 'dbo.Orders_Recovered', 'Orders';

-- Step 5: Bring YourDatabase back online
RESTORE DATABASE [YourDatabase] WITH RECOVERY;

Scenario D: Hardware Failure with AG Secondary Available

If your database participates in an Always On Availability Group, a hardware failure on the primary is not a disaster — it is a planned failover.

-- Force failover to secondary (when primary is completely unavailable)
-- Run on the secondary replica server
ALTER AVAILABILITY GROUP [YourAG] FORCE_FAILOVER_ALLOW_DATA_LOSS;

-- If primary was synchronous and fully caught up, there is zero data loss.
-- If asynchronous or lag existed, some transactions may be lost.

-- After failover, check the new primary
SELECT
    ag.name                 AS AvailabilityGroup,
    ar.replica_server_name  AS Replica,
    ars.role_desc           AS Role,
    ars.synchronization_health_desc,
    ars.connected_state_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id;

-- Update your application connection string to point to the new primary listener
-- Or update the AG listener DNS record

 10. Common Backup Mistakes (and How to Avoid Them)

-- Enable backup compression by default (SQL Server 2008+)
EXEC sp_configure 'backup compression default', 1;
RECONFIGURE;

-- Verify
SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'backup compression default';

-- =====================================================================
-- Backup Monitoring Query — Run Daily or via SQL Agent Alert Job
-- Identifies databases with stale or missing backups
-- =====================================================================
SELECT
    d.name                                              AS DatabaseName,
    d.recovery_model_desc                               AS RecoveryModel,
    MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) AS LastFullBackup,
    MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) AS LastDiffBackup,
    MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS LastLogBackup,
    DATEDIFF(HOUR,
        MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END),
        GETDATE())                                      AS HoursSinceFullBackup,
    CASE
        WHEN MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) IS NULL
            THEN 'CRITICAL: Never backed up'
        WHEN DATEDIFF(HOUR,
                MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END),
                GETDATE()) > 168
            THEN 'WARNING: Full backup older than 7 days'
        WHEN d.recovery_model_desc = 'FULL'
         AND DATEDIFF(HOUR,
                MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END),
                GETDATE()) > 2
            THEN 'WARNING: Log backup older than 2 hours on FULL model DB'
        ELSE 'OK'
    END                                                 AS BackupStatus
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset bs ON bs.database_name = d.name
WHERE d.database_id > 4                                  -- skip system DBs
  AND d.state_desc = 'ONLINE'
GROUP BY d.name, d.recovery_model_desc
ORDER BY
    CASE WHEN MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) IS NULL
         THEN 0 ELSE 1 END,
    HoursSinceFullBackup DESC;