SQL Serverrecovery guide

WITH LastBackups AS (
    SELECT
        bs.database_name,
        bs.type,
        MAX(bs.backup_finish_date) AS last_backup_finish_date
    FROM msdb.dbo.backupset AS bs
    WHERE bs.type IN ('D', 'I', 'L')
    GROUP BY bs.database_name, bs.type
)
SELECT
    d.name AS database_name,
    d.recovery_model_desc,
    d.state_desc,
    d.is_read_only,
    MAX(CASE WHEN lb.type = 'D' THEN lb.last_backup_finish_date END) AS last_full_backup,
    MAX(CASE WHEN lb.type = 'I' THEN lb.last_backup_finish_date END) AS last_differential_backup,
    MAX(CASE WHEN lb.type = 'L' THEN lb.last_backup_finish_date END) AS last_log_backup
FROM sys.databases AS d
LEFT JOIN LastBackups AS lb
    ON lb.database_name = d.name
WHERE d.name <> N'tempdb'
GROUP BY d.name, d.recovery_model_desc, d.state_desc, d.is_read_only
ORDER BY d.name;

DECLARE @database_name sysname = N'YourDatabase';
DECLARE @restore_start_time datetime2 = DATEADD(day, -7, SYSDATETIME());

SELECT
    bs.database_name,
    CASE bs.type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Log'
        ELSE bs.type
    END AS backup_type,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.first_lsn,
    bs.last_lsn,
    bs.database_backup_lsn,
    bs.checkpoint_lsn,
    bs.is_copy_only,
    bmf.physical_device_name
FROM msdb.dbo.backupset AS bs
LEFT JOIN msdb.dbo.backupmediafamily AS bmf
    ON bmf.media_set_id = bs.media_set_id
WHERE bs.database_name = @database_name
  AND bs.backup_finish_date >= @restore_start_time
  AND bs.type IN ('D', 'I', 'L')
ORDER BY
    bs.backup_finish_date,
    CASE bs.type WHEN 'D' THEN 1 WHEN 'I' THEN 2 WHEN 'L' THEN 3 ELSE 4 END;

-- Template only. Replace database names, file paths, STOPAT, and MOVE paths.
-- Keep NORECOVERY until every required backup has been applied.

RESTORE DATABASE YourDatabase_Restore
FROM DISK = N'X:\SqlBackups\YourDatabase_full.bak'
WITH NORECOVERY,
     MOVE N'YourDatabase' TO N'D:\SQLData\YourDatabase_Restore.mdf',
     MOVE N'YourDatabase_log' TO N'L:\SQLLogs\YourDatabase_Restore.ldf';

RESTORE DATABASE YourDatabase_Restore
FROM DISK = N'X:\SqlBackups\YourDatabase_diff.bak'
WITH NORECOVERY;

RESTORE LOG YourDatabase_Restore
FROM DISK = N'X:\SqlBackups\YourDatabase_log_001.trn'
WITH NORECOVERY;

RESTORE LOG YourDatabase_Restore
FROM DISK = N'X:\SqlBackups\YourDatabase_log_002.trn'
WITH RECOVERY;

-- Template only. Choose the target time from the incident timeline.
-- STOPAT applies to the log restore where the target time falls.

RESTORE DATABASE YourDatabase_Restore
FROM DISK = N'X:\SqlBackups\YourDatabase_full.bak'
WITH NORECOVERY;

RESTORE LOG YourDatabase_Restore
FROM DISK = N'X:\SqlBackups\YourDatabase_log_001.trn'
WITH STOPAT = '2026-05-20T14:35:00',
     RECOVERY;

DECLARE @database_name sysname = N'YourDatabase';

SELECT TOP (100)
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.first_lsn,
    bs.last_lsn,
    bs.database_backup_lsn,
    LAG(bs.last_lsn) OVER (
        PARTITION BY bs.database_name
        ORDER BY bs.backup_start_date
    ) AS previous_last_lsn,
    bs.is_copy_only
FROM msdb.dbo.backupset AS bs
WHERE bs.database_name = @database_name
  AND bs.type = 'L'
ORDER BY bs.backup_start_date;

-- Conditional incident template.
-- Use only after deciding the damaged source database should be touched.

BACKUP LOG YourDatabase
TO DISK = N'X:\SqlBackups\YourDatabase_tail_20260520.trn'
WITH NORECOVERY,
     CHECKSUM;

SELECT TOP (100)
    rh.destination_database_name,
    rh.restore_date,
    rh.restore_type,
    rh.replace,
    rh.recovery,
    bs.database_name AS source_database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    CASE bs.type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Log'
        ELSE bs.type
    END AS backup_type,
    rf.destination_phys_name
FROM msdb.dbo.restorehistory AS rh
LEFT JOIN msdb.dbo.backupset AS bs
    ON bs.backup_set_id = rh.backup_set_id
LEFT JOIN msdb.dbo.restorefile AS rf
    ON rf.restore_history_id = rh.restore_history_id
ORDER BY rh.restore_date DESC;

DECLARE @database_name sysname = N'YourDatabase';

SELECT TOP (100)
    bs.backup_set_id,
    bs.database_name,
    CASE bs.type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Log'
        ELSE bs.type
    END AS backup_type,
    bs.backup_finish_date,
    bs.first_lsn,
    bs.last_lsn,
    bs.is_copy_only,
    bs.has_backup_checksums,
    bmf.physical_device_name,
    bmf.device_type
FROM msdb.dbo.backupset AS bs
JOIN msdb.dbo.backupmediafamily AS bmf
    ON bmf.media_set_id = bs.media_set_id
WHERE bs.database_name = @database_name
ORDER BY bs.backup_finish_date DESC;

SELECT TOP (100)
    rh.destination_database_name,
    rh.restore_date,
    rh.restore_type,
    bs.database_name AS source_database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date) AS source_backup_duration_seconds,
    CONVERT(decimal(18,2), bs.backup_size / 1024.0 / 1024.0) AS backup_size_mb,
    CONVERT(decimal(18,2), bs.compressed_backup_size / 1024.0 / 1024.0) AS compressed_size_mb
FROM msdb.dbo.restorehistory AS rh
LEFT JOIN msdb.dbo.backupset AS bs
    ON bs.backup_set_id = rh.backup_set_id
ORDER BY rh.restore_date DESC;

DECLARE @database_name sysname = N'YourDatabase';

SELECT
    N'SQL Agent jobs' AS dependency_type,
    COUNT(*) AS item_count,
    STRING_AGG(CONVERT(nvarchar(max), j.name), N'; ') AS sample_items
FROM msdb.dbo.sysjobs AS j
WHERE j.enabled = 1

UNION ALL

SELECT
    N'Linked servers' AS dependency_type,
    COUNT(*) AS item_count,
    STRING_AGG(CONVERT(nvarchar(max), s.name), N'; ') AS sample_items
FROM sys.servers AS s
WHERE s.is_linked = 1

UNION ALL

SELECT
    N'Credentials' AS dependency_type,
    COUNT(*) AS item_count,
    STRING_AGG(CONVERT(nvarchar(max), c.name), N'; ') AS sample_items
FROM sys.credentials AS c

UNION ALL

SELECT
    N'Orphaned database users in selected database' AS dependency_type,
    COUNT(*) AS item_count,
    STRING_AGG(CONVERT(nvarchar(max), dp.name), N'; ') AS sample_items
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp
    ON sp.sid = dp.sid
WHERE DB_NAME() = @database_name
  AND dp.type IN ('S', 'U', 'G')
  AND dp.sid IS NOT NULL
  AND sp.sid IS NULL
  AND dp.name NOT IN (N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys');

SELECT TOP (100)
    j.name AS job_name,
    h.step_id,
    h.step_name,
    h.run_status,
    DATETIMEFROMPARTS(
        h.run_date / 10000,
        (h.run_date % 10000) / 100,
        h.run_date % 100,
        h.run_time / 10000,
        (h.run_time % 10000) / 100,
        h.run_time % 100,
        0
    ) AS run_start_time,
    ((h.run_duration / 10000) * 3600)
      + (((h.run_duration % 10000) / 100) * 60)
      + (h.run_duration % 100) AS run_duration_seconds,
    h.message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j
    ON j.job_id = h.job_id
WHERE h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -30, SYSDATETIME()), 112))
  AND (
      j.name LIKE N'%backup%'
      OR j.name LIKE N'%restore%'
      OR j.name LIKE N'%recovery%'
      OR j.name LIKE N'%drill%'
      OR h.step_name LIKE N'%restore%'
  )
ORDER BY run_start_time DESC;