How do you know your SQL Server backups actually work?

    Last updated · DB24 Team

    Look at every database, not at the backup job. SQL Server records each successful backup in msdb.dbo.backupset, so comparing it with sys.databases shows which databases have a recent backup of each type and whether any backup was flagged as damaged. RESTORE VERIFYONLY then checks that a backup file is complete and readable, and only a timed restore followed by DBCC CHECKDB shows that the data comes back in time.

    Key takeaways

    • A backup job reports on the job. msdb.dbo.backupset has one row per successful backup per database, and that is where monitoring should look.
    • Missed, late, incomplete and damaged backups, stopped log backups, broken log chains and silently stopped jobs all leave a trace in SQL Server. A scheduled check can catch most of them.
    • Thresholds come from the data loss and downtime the business accepts. Microsoft ties log backup frequency to your recovery point and recovery time objectives (RPO and RTO).
    • RESTORE VERIFYONLY checks that a backup is complete and readable, not the structure of the data in it. Microsoft's own guidance: "You don't have a restore strategy until you test your backups."

    What can go wrong with a SQL Server backup?

    Each deviation below leaves a trace somewhere in SQL Server. The last column says whether a scheduled check can raise the alert without anyone reading a report.

    SQL Server backup deviations: how to detect them and whether they can be alerted on
    Deviation How you detect it in SQL Server What happens if nobody notices Can it be alerted on automatically?
    Missed backup Compare sys.databases with the newest backupset row per database and type. A failed backup writes no row The newest copy is older than anyone thinks; a database added after the job was set up may never have had one Yes
    Late backup backup_finish_date of the newest backup per database and type, against the threshold A restore loses more data than the business accepts Yes
    Log backups stopped (full or bulk-logged recovery) log_reuse_wait_desc shows LOG_BACKUP in sys.databases; no recent backupset rows of type L The log cannot be truncated and grows; if it fills (error 9002) the database can be read but not updated Yes
    Broken log chain Each log backup's first_lsn should match the previous one's last_lsn; begins_log_chain = 1 marks a new chain A restore can only go as far as the last log backup before the gap Partly: gaps in the history yes, a deleted file no
    Incomplete backup RESTORE VERIFYONLY on the file; backupmediafamily shows where every file was written; backup_size against earlier backups The restore fails on the day it is needed Partly
    Damaged backup is_damaged = 1 in backupset; RESTORE VERIFYONLY WITH CHECKSUM on the file Damaged pages are restored, or the restore fails Partly
    Backup in an unexpected place physical_device_name in backupmediafamily; is_copy_only in backupset An ad hoc full backup without COPY_ONLY becomes the base for later differentials, which then need a file the plan does not know about Yes
    Job silently stopped enabled in sysjobs and sysschedules, next_run_date in sysjobschedules, sysjobhistory, and SQL Server Agent's status in sys.dm_server_services Nothing fails, so no failure notification goes out; backups just get older Yes, through backup age
    Restore too slow or data unusable Only a timed restore followed by DBCC CHECKDB Found during an incident No

    Where do the answers live in SQL Server?

    • msdb.dbo.backupset: one row per backup set, which Microsoft defines as "the backup from a single, successful backup operation". It holds type (D full, I differential, L log), backup_finish_date, backup_size, is_damaged, has_backup_checksums, is_copy_only and the LSNs. Microsoft says the complete backup and restore history of an instance is stored in msdb and recommends backing up msdb frequently. History trimmed with sp_delete_backuphistory is gone from the check too.
    • msdb.dbo.backupmediafamily: physical_device_name shows where each backup was written.
    • sys.databases: the list to compare against, with the recovery model and log_reuse_wait_desc. LOG_BACKUP means "A log backup is required before the transaction log can be truncated."
    • SQL Server Agent: a disabled job or schedule simply does not run; Microsoft notes that "If the schedule is not enabled, no jobs will run on the schedule" (sysschedules). Job notifications go out when a job succeeds, fails or completes, so a job that never starts sends nothing.
    • The backup file: RESTORE HEADERONLY returns the header, including LSNs, HasBackupChecksums and IsDamaged. RESTORE FILELISTONLY lists the data and log files in the backup with their sizes, which tells you how much space a restore needs.

    How far do RESTORE VERIFYONLY and CHECKSUM go?

    RESTORE VERIFYONLY checks that the backup set is complete and all volumes are readable, some page header fields, the checksum if present, and space on the destination. It "does not attempt to verify the structure of the data contained in the backup volumes."

    The checksum matters. BACKUP uses NO_CHECKSUM by default, except for compressed backups (Microsoft). Without a backup checksum, restore "cannot reliably verify page checksums" (media errors). The backup checksum default option turns it on for every backup on an instance. Microsoft notes a possible CPU and throughput cost, so watch the load when you enable it.

    What threshold should trigger an alert?

    Microsoft sets no fixed alert threshold. A backup strategy "balances the business requirements for maximum data availability and minimum data loss against the cost of maintaining and storing backups" (Microsoft). For log backups, Microsoft says to "Take frequent enough log backups to support your business requirements" and to think about RTO and RPO; it adds that "Taking a log backup every 15 to 30 minutes might be enough" (transaction log backups).

    In practice: agree the accepted data loss per system, schedule each backup type to meet it, and alert per database and backup type when the newest backup is older than the schedule allows. Separate warning and critical levels help.

    What can only a restore show?

    1. That the full backup, the latest differential and every log backup since can be restored in order. If a log backup in the chain is lost or damaged, "you can restore only transaction logs before the missing transaction log".
    2. How long the restore takes, compared with the downtime the business accepts.
    3. That the restored database is consistent. Microsoft says to run DBCC CHECKDB after restoring a test copy.
    4. For encrypted backups, that the certificate or key is on the server you restore to. Without it, "you can't restore the backup" (backup encryption).
    5. That the record is kept. Microsoft recommends an operations manual per database with backup locations and "the time required to restore the test backups".

    Whether an attacker could reach the copies is covered in When backups fail during a ransomware attack. Under NIS2, backup history and dated restore tests become evidence (NIS2 and SQL Server), and backups head the list in How to run SQL Server without a DBA.

    How DB24 helps

    DB24 keeps watch over the backups on every instance and alerts when one fails, falls behind, deviates in size or is flagged as damaged, so whoever owns SQL Server hears about it the day it happens rather than during a restore:

    • Backup status per database: DB24 shows backup state across the estate, also when another tool takes the backups (DB24 Backup).
    • Alerts on deviations: the Backup Warning alert covers failed backups, backups older than the thresholds you set, size deviations, backups flagged as damaged, and, with DB24 Backup enabled, backups written to a different path than the configured one. Thresholds have warning and critical levels (notifications).
    • Backups taken by DB24: full, differential and log backups per instance, with automatic retries. If there is no usable full backup to start a log chain, DB24 can take one before the log backups. With verification on, DB24 runs RESTORE VERIFYONLY after each backup it takes; no data is restored.
    • Cleanup that keeps the chain: old backups are deleted only once a newer successful backup of the same type exists, and never if they belong to the latest backup chain.
    • A record over time: daily and monthly briefs summarise the environment.

    DB24 is priced per SQL Server instance. Contact us for a quote.

    Full restore tests stay with your team; DB24 shows what there is to restore and keeps the record.

    To see it on your own instances, contact us.

    Frequently asked questions

    Does RESTORE VERIFYONLY prove that a backup can be restored?

    No. It checks that the backup set is complete and readable, and verifies checksums if the backup has them. Microsoft says it does not verify the structure of the data. Only a restore followed by DBCC CHECKDB shows that.

    How often should we check our SQL Server backups?

    After every scheduled backup, and at least daily. The check itself is a query against msdb and sys.databases on each instance. How old a backup may get before it counts as late is set by the data loss each system can accept.

    Should our backups use WITH CHECKSUM?

    Usually yes. A backup taken WITH CHECKSUM fails on a page checksum error instead of copying it, and later restores can verify the file. Compressed backups use checksums by default. Test the CPU cost on busy instances first.

    We use a third-party backup tool. What should we monitor?

    Look in msdb.dbo.backupset anyway. In backupmediafamily, device_type shows whether a backup went to disk, a virtual device or Azure Storage, and backupset marks snapshot backups with is_snapshot. If a database has no rows at all, ask the tool's owner where its backups are recorded.

    How much does DB24 cost?

    DB24 is priced per SQL Server instance. Contact us for a quote.

    Sources

    1. Microsoft Learn, backupset
    2. Microsoft Learn, backupmediafamily
    3. Microsoft Learn, Backup history and header information
    4. Microsoft Learn, sys.databases
    5. Microsoft Learn, The transaction log
    6. Microsoft Learn, Troubleshoot a full transaction log (error 9002)
    7. Microsoft Learn, Transaction log backups
    8. Microsoft Learn, Apply transaction log backups
    9. Microsoft Learn, Transaction log architecture and management guide
    10. Microsoft Learn, Differential backups
    11. Microsoft Learn, Copy-only backups
    12. Microsoft Learn, RESTORE VERIFYONLY
    13. Microsoft Learn, RESTORE HEADERONLY
    14. Microsoft Learn, RESTORE FILELISTONLY
    15. Microsoft Learn, Enable or disable backup checksums
    16. Microsoft Learn, Possible media errors during backup and restore
    17. Microsoft Learn, backup checksum default
    18. Microsoft Learn, BACKUP (Transact-SQL)
    19. Microsoft Learn, dbo.sysjobs
    20. Microsoft Learn, dbo.sysschedules
    21. Microsoft Learn, dbo.sysjobschedules
    22. Microsoft Learn, dbo.sysjobhistory
    23. Microsoft Learn, sys.dm_server_services
    24. Microsoft Learn, Notify an operator of job status
    25. Microsoft Learn, Back up and restore of SQL Server databases
    26. Microsoft Learn, Backup encryption
    27. DB24 Docs, DB24 Backup
    28. DB24 Docs, Notifications