SQL Serverbackup guide

SELECT TOP (100)
    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,
    DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date) AS 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,
    bs.is_copy_only,
    bs.has_backup_checksums,
    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.backup_start_date >= DATEADD(day, -14, SYSDATETIME())
ORDER BY bs.backup_start_date DESC;

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,
    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
ORDER BY d.name;

WITH LastFull AS (
    SELECT
        bs.database_name,
        MAX(bs.backup_finish_date) AS last_full_backup
    FROM msdb.dbo.backupset AS bs
    WHERE bs.type = 'D'
    GROUP BY bs.database_name
)
SELECT
    d.name AS database_name,
    d.recovery_model_desc,
    lf.last_full_backup,
    DATEDIFF(hour, lf.last_full_backup, SYSDATETIME()) AS hours_since_last_full
FROM sys.databases AS d
LEFT JOIN LastFull AS lf
    ON lf.database_name = d.name
WHERE d.name <> N'tempdb'
  AND (
      lf.last_full_backup IS NULL
      OR lf.last_full_backup < DATEADD(day, -1, SYSDATETIME())
  )
ORDER BY lf.last_full_backup;

WITH LastLog AS (
    SELECT
        bs.database_name,
        MAX(bs.backup_finish_date) AS last_log_backup
    FROM msdb.dbo.backupset AS bs
    WHERE bs.type = 'L'
    GROUP BY bs.database_name
)
SELECT
    d.name AS database_name,
    d.recovery_model_desc,
    ll.last_log_backup,
    DATEDIFF(minute, ll.last_log_backup, SYSDATETIME()) AS minutes_since_last_log
FROM sys.databases AS d
LEFT JOIN LastLog AS ll
    ON ll.database_name = d.name
WHERE d.name <> N'tempdb'
  AND d.recovery_model_desc IN (N'FULL', N'BULK_LOGGED')
  AND (
      ll.last_log_backup IS NULL
      OR ll.last_log_backup < DATEADD(hour, -1, SYSDATETIME())
  )
ORDER BY ll.last_log_backup;

SELECT TOP (100)
    bs.database_name,
    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,
    LAG(bs.last_lsn) OVER (
        PARTITION BY bs.database_name
        ORDER BY bs.backup_start_date
    ) AS previous_last_lsn
FROM msdb.dbo.backupset AS bs
WHERE bs.type = 'L'
  AND bs.backup_start_date >= DATEADD(day, -7, SYSDATETIME())
ORDER BY bs.database_name, bs.backup_start_date;

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;

-- Choose the exact backup file first.
-- Confirm SQL Server can read the path and that CHECKSUM was used when possible.
RESTORE VERIFYONLY
FROM DISK = N'X:\SqlBackups\YourDatabase_full_20260520.bak'
WITH CHECKSUM;

SELECT TOP (100)
    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,
    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,
    CONVERT(decimal(18,2),
        bs.backup_size / NULLIF(bs.compressed_backup_size, 0)
    ) AS compression_ratio,
    bs.has_backup_checksums,
    bs.is_password_protected,
    bs.encryptor_type,
    bs.key_algorithm,
    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.backup_finish_date >= DATEADD(day, -30, SYSDATETIME())
ORDER BY bs.backup_finish_date DESC;

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, -14, SYSDATETIME()), 112))
  AND (
      h.run_status <> 1
      OR j.name LIKE N'%backup%'
      OR h.step_name LIKE N'%backup%'
  )
ORDER BY run_start_time DESC;