Part of the DBA-Tools Project.
You can have a full backup from last night and still not be able to restore to 2pm today. That’s the gap this script closes. Backup coverage checks tell you when your last backups ran. They don’t tell you whether the log backups between them actually form an unbroken chain.
A single missing or damaged log backup breaks point-in-time restore for that entire window, even if every backup job shows green in your monitoring. You usually don’t find out until you’re mid-incident, restoring toward a specific time, and SQL Server tells you the chain won’t get there.
This script walks the LSN (Log Sequence Number) chain from the last full backup through every log backup since, flags any gap or damaged backup set, and calls out databases with no full backup on record at all.
Why Backup Chain Integrity Matters
Point-in-time restore depends on LSN continuity, not backup frequency. SQL Server needs an unbroken sequence from a full backup through every log backup up to the point you’re restoring to. If one log backup in that sequence is missing, damaged, or was taken outside the chain (for example after someone ran BACKUP LOG ... WITH NORECOVERY or truncated the log outside of a backup), everything after the gap becomes unrestorable to a specific point in time.
This matters most for:
- Databases in the FULL or BULK_LOGGED recovery model, where point-in-time restore is the entire reason you’re paying the log backup overhead
- Any database with a compliance or RPO requirement that assumes a working log chain
- Post-migration and post-restore scenarios, where a chain can look intact in coverage reports but actually restart from a new full backup you didn’t expect
- Availability Groups and log shipping, which depend on the same LSN continuity to keep replicas in sync
A backup coverage report can look perfectly healthy while the underlying chain is already broken. Coverage tells you a backup happened. This script tells you whether you could actually use it.
When to Run This Script
- Routine SQL Server health checks, alongside backup coverage
- Before and after any change to backup job schedules or retention
- Before a disaster recovery test, so you’re not discovering a broken chain during the test itself
- After a migration or restore, to confirm the new chain actually started cleanly
- Whenever a database’s recovery model was recently changed to FULL or BULK_LOGGED
The Script
Run the following script against your SQL Server instance.
/*
Script Name : Get-BackupChainIntegrity
Category : backups
Purpose : LSN continuity analysis for each user database. Verifies the log backup chain
from the most recent full backup to now is unbroken. A gap in the log chain
means point-in-time restore is impossible for that window — coverage scripts
only check recency, not continuity. Also surfaces damaged backup sets.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-backup-chain-integrity/)
Requires : SELECT on msdb, VIEW ANY DATABASE
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
WITH
last_full AS (
SELECT
bs.database_name,
MAX(bs.backup_set_id) AS backup_set_id
FROM msdb.dbo.backupset AS bs
WHERE bs.type = 'D'
AND bs.database_name NOT IN ('master','model','msdb','tempdb')
GROUP BY bs.database_name
),
full_details AS (
SELECT
bs.database_name,
bs.backup_set_id AS full_backup_set_id,
bs.backup_start_date AS full_backup_start,
bs.backup_finish_date AS full_backup_finish,
bs.first_lsn AS full_first_lsn,
bs.last_lsn AS full_last_lsn,
bs.is_damaged AS full_is_damaged,
bs.has_incomplete_metadata AS full_incomplete_metadata,
bs.compressed_backup_size / 1024.0 / 1024.0 AS full_backup_size_mb,
bmf.physical_device_name AS full_backup_file
FROM last_full lf
JOIN msdb.dbo.backupset AS bs ON bs.backup_set_id = lf.backup_set_id
LEFT JOIN msdb.dbo.backupmediafamily AS bmf ON bmf.media_set_id = bs.media_set_id
),
log_chain AS (
SELECT
bs.database_name,
bs.backup_set_id,
bs.backup_start_date,
bs.first_lsn,
bs.last_lsn,
bs.is_damaged,
ROW_NUMBER() OVER (PARTITION BY bs.database_name ORDER BY bs.first_lsn) AS log_seq,
LAG(bs.last_lsn) OVER (PARTITION BY bs.database_name ORDER BY bs.first_lsn) AS prev_last_lsn
FROM msdb.dbo.backupset AS bs
JOIN full_details AS fd ON fd.database_name = bs.database_name
WHERE bs.type = 'L'
AND bs.first_lsn >= fd.full_last_lsn
),
log_gaps AS (
SELECT
database_name,
COUNT(*) AS log_backup_count,
SUM(CASE WHEN is_damaged = 1 THEN 1 ELSE 0 END) AS damaged_log_backups,
SUM(CASE
WHEN log_seq > 1 AND prev_last_lsn IS NOT NULL AND first_lsn > prev_last_lsn + 1
THEN 1 ELSE 0 END) AS chain_gaps,
MIN(CASE
WHEN log_seq > 1 AND prev_last_lsn IS NOT NULL AND first_lsn > prev_last_lsn + 1
THEN backup_start_date END) AS first_gap_at
FROM log_chain
GROUP BY database_name
),
last_log AS (
SELECT
bs.database_name,
MAX(bs.backup_finish_date) AS last_log_backup_finish,
MAX(bs.last_lsn) AS last_log_lsn
FROM msdb.dbo.backupset AS bs
WHERE bs.type = 'L'
AND bs.database_name NOT IN ('master','model','msdb','tempdb')
GROUP BY bs.database_name
),
-- Combine everything before ordering (avoids alias-in-UNION ORDER BY restriction)
combined AS (
SELECT
fd.database_name,
fd.full_backup_start,
fd.full_backup_finish,
CAST(fd.full_backup_size_mb AS DECIMAL(12,2)) AS full_backup_size_mb,
DATEDIFF(HOUR, fd.full_backup_finish, GETDATE()) AS full_backup_age_hours,
fd.full_is_damaged,
fd.full_incomplete_metadata,
ISNULL(lg.log_backup_count, 0) AS log_backups_since_full,
ISNULL(lg.damaged_log_backups, 0) AS damaged_log_backups,
ISNULL(lg.chain_gaps, 0) AS log_chain_gaps,
lg.first_gap_at,
ll.last_log_backup_finish,
DATEDIFF(MINUTE, ll.last_log_backup_finish, GETDATE()) AS log_backup_age_minutes,
d.recovery_model_desc,
d.log_reuse_wait_desc,
CASE
WHEN fd.full_is_damaged = 1
THEN 'CRITICAL — last full backup is marked damaged in msdb'
WHEN ISNULL(lg.chain_gaps, 0) > 0
THEN 'CRITICAL — ' + CAST(lg.chain_gaps AS VARCHAR) +
' gap(s) in log chain since last full; PITR impossible for those windows (gap starts ' +
CONVERT(VARCHAR(20), lg.first_gap_at, 120) + ')'
WHEN d.recovery_model_desc = 'FULL' AND ll.last_log_backup_finish IS NULL
THEN 'WARN — FULL recovery model but no log backups since last full'
WHEN d.recovery_model_desc = 'FULL'
AND DATEDIFF(MINUTE, ll.last_log_backup_finish, GETDATE()) > 60
THEN 'WARN — last log backup is ' +
CAST(DATEDIFF(MINUTE, ll.last_log_backup_finish, GETDATE()) AS VARCHAR) +
' minutes old; RPO exposure growing'
WHEN ISNULL(lg.damaged_log_backups, 0) > 0
THEN 'WARN — ' + CAST(lg.damaged_log_backups AS VARCHAR) + ' log backup(s) marked damaged'
ELSE 'OK — chain is intact'
END AS chain_status,
LEFT(fd.full_backup_file, 200) AS full_backup_file_path
FROM full_details AS fd
LEFT JOIN log_gaps AS lg ON lg.database_name = fd.database_name
LEFT JOIN last_log AS ll ON ll.database_name = fd.database_name
LEFT JOIN sys.databases AS d ON d.name = fd.database_name
UNION ALL
SELECT
d.name, NULL, NULL, NULL, NULL, NULL, NULL,
0, 0, 0, NULL, NULL, NULL,
d.recovery_model_desc, d.log_reuse_wait_desc,
'CRITICAL — no full backup on record for this database',
NULL
FROM sys.databases AS d
WHERE d.database_id > 4
AND d.state = 0
AND d.name NOT IN (SELECT database_name FROM full_details)
)
SELECT *
FROM combined
ORDER BY
CASE WHEN chain_status LIKE 'CRITICAL%' THEN 1
WHEN chain_status LIKE 'WARN%' THEN 2
ELSE 3 END,
database_name;
The script walks msdb.dbo.backupset for each database, finds the most recent full backup, then checks every log backup since for LSN continuity, damaged flags, and staleness.
How To Run From The Repo
Clone DBA Tools, initialize and run the script:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Check backup chain integrity across all user databases:
.\run.ps1 Get-BackupChainIntegrity
# To run against a remote sql server:
.\run.ps1 Get-BackupChainIntegrity -ServerInstance SQLSERVER01
This script lives in the repo at:
Example Output
| Database | Recovery Model | Full Backup Age | Chain Gaps | Status |
|---|---|---|---|---|
| migdb_37A45A06 (and 4 more migdb_* hex-named) | FULL | none on record | 0 | CRITICAL: no full backup on record |
| DemoDatabase, GrowthLab, testlocal_001 to testlocal_009 (11 total) | FULL | 1315 hours | 0 | WARN: FULL recovery model but no log backups since last full |
| RandomLab | FULL | 1315 hours | 0 | WARN: last log backup is 238432 minutes old (~166 days); RPO exposure growing |
| migdb_001 to migdb_010 series, SalesDemo, WatchtowerMetrics (42 total) | FULL / SIMPLE | 1315 hours | 0 | OK: chain is intact |
Real output, captured against a local lab instance carrying 60 user databases, mostly migration leftovers. The picture is realistic for an inherited server: most chains (42) are fine, but 5 databases have no full backup on record at all, and 12 more are in the FULL recovery model paying the log growth overhead with no log backup ever taken, or one badly stale. None of that shows up in a plain backup coverage report if it only checks “was a full backup taken recently,” because these databases were never in the backup schedule to begin with.
Understanding the Results
The chain_status column is the one to read first. It’s ranked CRITICAL, then WARN, then OK, so the worst problems sort to the top.
CRITICAL: no full backup on record. There’s nothing to build a chain from. Any log backups that exist for this database are orphaned. This is the most urgent finding on the list; the database has no restorable backup at all.
CRITICAL: gap(s) in log chain since last full. The LSN sequence breaks somewhere between two log backups. Point-in-time restore is impossible across that gap, even though backups either side of it exist and look fine individually.
WARN: FULL recovery model but no log backups since last full. The database is paying the FULL recovery model’s transaction log overhead without getting the point-in-time restore benefit it exists for. This usually means the log backup job was never configured for this database, or it was added to the instance after the job was set up.
WARN: last log backup is stale. Log backups are running, but not recently enough. Every minute since the last one is exposure: if you had to restore right now, you’d lose everything since that backup.
OK: chain is intact. Full backup exists, log backups are continuous since it, and the most recent one is current. This is the state every FULL recovery model database should be in.
Common Causes
The pattern in the example output is a common one: databases added to an instance after the backup jobs were configured. A new database defaults to whatever recovery model the model database uses (often FULL), the scheduled full and log backup jobs don’t automatically pick it up, and it sits invisible until someone runs a chain integrity check or, worse, needs to restore it.
Other common causes:
- Migration or restore leftovers (like the
migdb_*databases here) that were never folded into the production backup schedule - A log backup job that’s failing silently, especially if job failure alerts aren’t wired up
- Someone switching a database’s recovery model to FULL for a one-off requirement and never adding log backups
- A log chain manually broken by
BACKUP LOG ... WITH NORECOVERY, a log shrink outside of a backup operation, or restoring over a database without reinitializing its backup schedule
How to Fix a Broken or Missing Backup Chain
The fix depends on which finding you’re looking at.
No full backup on record. Take one now. There’s no chain to repair, only one to start:
BACKUP DATABASE [YourDatabase]
TO DISK = N'D:\SQL-Backups\YourDatabase_FULL.bak'
WITH COMPRESSION, CHECKSUM;
FULL recovery model with no log backups. Either start taking log backups on a schedule, or switch to SIMPLE recovery if point-in-time restore genuinely isn’t a requirement for this database. Don’t leave it in FULL with no log backup job; that configuration gives you all of the log growth overhead and none of the recovery benefit.
-- Option A: start log backups (add this to your scheduled backup job)
BACKUP LOG [YourDatabase]
TO DISK = N'D:\SQL-Backups\YourDatabase_LOG.trn'
WITH COMPRESSION, CHECKSUM;
-- Option B: switch to SIMPLE if PITR isn't required
ALTER DATABASE [YourDatabase] SET RECOVERY SIMPLE;
A gap in the log chain. You can’t repair a gap after the fact. Take a fresh full backup to restart the chain, then resume log backups from there on schedule.
Whichever fix applies, run this script again afterward to confirm the chain is intact before you consider the database covered.
Best Practices
- Run backup coverage and backup chain integrity together. Coverage tells you a backup happened; this script tells you whether it’s usable
- Alert on new databases the day they appear, so they get added to the backup schedule immediately rather than discovered later
- Alert on log backup job failures directly, don’t rely on periodically noticing a stale chain
- Treat any FULL recovery model database with no scheduled log backups as a finding, not a curiosity
- Verify the chain after any migration, restore, or manual log operation, not just on the regular schedule
Good backup management catches a broken chain before you need it, not during the restore.
Related Scripts
You may also find these scripts useful:
- Backup Coverage
- Backup Encryption Status
- Backup Restore Duration Estimate
- Backup Size Trend
- Database Backup History
- Last Database Backup Times
- Backup and Restore Progress
Frequently Asked Questions
What is a backup chain in SQL Server?
It’s the unbroken LSN sequence from a full backup through every log backup taken since. Point-in-time restore needs every link in that chain present and undamaged, not just a recent backup.
What’s the difference between backup coverage and backup chain integrity?
Coverage answers “did a backup run recently.” Chain integrity answers “could I actually restore to a specific point in time using what I have.” A database can pass coverage checks and still have a broken chain.
Does this matter for SIMPLE recovery model databases?
Not in the same way. SIMPLE recovery model databases don’t support log backups or point-in-time restore, so chain integrity is a FULL and BULK_LOGGED recovery model concern.
Can I fix a broken log chain without taking a new full backup?
No. Once the LSN sequence breaks, nothing after the gap can be restored to a point in time using the old chain. A fresh full backup is the only way to restart it.
Summary
Backup coverage tells you a backup happened. It doesn’t tell you whether you could actually restore to a specific point in time with what you have. This script checks the part that coverage reports miss: whether the LSN chain from your last full backup through every log backup since is actually intact.
Run it alongside your regular coverage checks, not instead of them. A clean coverage report and a broken chain can coexist quietly for months, right up until you need to restore and find out the hard way.
Add this to your routine health checks, and re-run it after any backup job change, migration, or restore, so a broken chain gets found on your schedule rather than during an incident.
Leave a Reply