DBA Scripts: Get Last Database Backup Times

Part of the DBA-Tools Project.

---

Checking the last backup times is one of the first things worth doing when you sit down at a SQL Server instance, whether it’s one you’ve owned for years or one you inherited an hour ago. Before making changes, running maintenance, or troubleshooting an issue, you need to know whether a reliable recovery point actually exists.

This script shows the most recent full, differential, and log backup for every database in one pass, so you can see the whole instance’s backup posture at a glance rather than checking databases one at a time.

---

Why Last Database Backup Times Matters

Backups are easy to assume exist and expensive to discover missing:

• Taking ownership of a new or inherited instance is the moment you most need an honest answer, not an assumed one
• Investigating recovery options during an incident starts here, before anything else
• Verifying backup jobs after a change or a reported failure needs a direct check, not a dashboard that might be stale
• Confirming coverage before maintenance or a migration avoids finding out about a gap at the worst possible time

If a database has no recent full backup, everything else on the list is a secondary priority.

---

When to Run This Script

• Routine SQL Server health checks
• Taking ownership of a new or inherited instance
• Investigating recovery options during an incident
• Verifying backup jobs after changes or a reported failure
• Confirming backup coverage before maintenance or a migration

---

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-LastDatabaseBackupTimes
Category    : backups-and-recovery
Purpose     : Display the latest backup timestamp per type (Full, Differential, Log) per database.
Author      : Peter Whyte (https://sqldba.blog/get-last-database-backup-times-in-sql-server/)
Requires    : db_datareader on msdb
HealthCheck : Yes
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

WITH latest_backups AS (
    SELECT
        bs.database_name,
        bs.type,
        bs.backup_finish_date,
        bs.backup_size / 1024.0 / 1024 AS backup_size_mb,
        ROW_NUMBER() OVER (
            PARTITION BY bs.database_name, bs.type
            ORDER BY bs.backup_finish_date DESC
        ) AS rn
    FROM msdb.dbo.backupset AS bs
)
SELECT
    d.name AS database_name,
    d.recovery_model_desc,
    MAX(CASE WHEN lb.type = 'D' THEN lb.backup_finish_date END) AS last_full_backup,
    MAX(CASE WHEN lb.type = 'D' THEN DATEDIFF(HOUR, lb.backup_finish_date, GETDATE()) END) AS full_backup_age_hours,
    MAX(CASE WHEN lb.type = 'D' THEN lb.backup_size_mb END) AS full_backup_size_mb,
    MAX(CASE WHEN lb.type = 'I' THEN lb.backup_finish_date END) AS last_diff_backup,
    MAX(CASE WHEN lb.type = 'I' THEN DATEDIFF(HOUR, lb.backup_finish_date, GETDATE()) END) AS diff_backup_age_hours,
    MAX(CASE WHEN lb.type = 'L' THEN lb.backup_finish_date END) AS last_log_backup,
    MAX(CASE WHEN lb.type = 'L' THEN DATEDIFF(HOUR, lb.backup_finish_date, GETDATE()) END) AS log_backup_age_hours
FROM sys.databases AS d
LEFT JOIN latest_backups AS lb
    ON d.name = lb.database_name
   AND lb.rn = 1
WHERE d.database_id > 4
GROUP BY d.name, d.recovery_model_desc
ORDER BY d.name;

The script returns one row per database, with the most recent full, differential, and log backup timestamps and ages side by side.

---

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

# Show last full, differential, and log backup times for all databases:
.\run.ps1 Get-LastDatabaseBackupTimes

# To run against a remote sql server:
.\run.ps1 Get-LastDatabaseBackupTimes -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/backups/Get-LastDatabaseBackupTimes.sql
• powershell/wrappers/backups/Get-LastDatabaseBackupTimes.ps1

---

Example Output

Real output, captured against a local lab instance with 20 user databases right now. Five have no full backup on record at all. The rest split into two age bands: a handful backed up 28 hours ago, and the majority sitting at 1343 hours, roughly 56 days. None of the FULL recovery model databases here have a log backup less than about 166 days old. A single glance at this table tells you more about this instance’s real backup posture than any dashboard showing “last job status: succeeded” would.

---

Understanding the Results

There’s no single pass/fail column here; read the three backup columns together for each database.

No value in last_full_backup means no full backup is on record for that database. There is currently no way to recover it, and that’s the first thing to fix.

A large full_backup_age_hours relative to your expected schedule is the next thing to check. A database on a daily schedule showing an age in the thousands of hours has clearly stopped being backed up, not just fallen slightly behind.

No value in last_log_backup for a FULL recovery model database means point-in-time recovery isn’t currently possible for it, regardless of how recent the full backup is. This is easy to miss when scanning quickly, since the full backup column can look perfectly healthy on its own.

A recent-looking full backup with a stale differential or log backup is still a real gap. Each backup type needs to be checked on its own terms.

---

Best Practices

• Check this on every new or inherited instance before doing anything else
• Don’t stop at the full backup column; a FULL recovery model database with no log backups is still exposed
• Cross-check any surprising result against msdb retention settings, since aggressive history cleanup can make a genuinely backed-up database look worse than it is
• For a scored, worst-first view instead of a flat list, pair this with Get Backup Coverage, which adds a status flag on top of the same underlying data
• For full historical detail rather than just the latest backup, pair this with Get Database Backup History

---

Related Scripts

You may also find these scripts useful:

• Backup Coverage
• Database Backup History
• Backup Chain Integrity
• Backup Encryption Status
• Backup and Restore Duration Estimate
• Generate Backup and Restore Scripts
• Backup and Restore Progress

---

Frequently Asked Questions

How often should I check last backup times?

As part of every routine health check, and always as a first step on any server you didn’t personally configure. It takes one query and answers the single most important question about a database’s recoverability.

Does a recent full backup mean the database is fully protected?

Not on its own. A FULL recovery model database also needs regular log backups for point-in-time recovery. Check all three columns, full, differential, and log, not just the full backup date.

Why would msdb show no backup for a database I know was backed up?

Backup history in msdb can be pruned by cleanup jobs or maintenance plans. If a result looks wrong, check the actual backup files on disk before assuming the backup never happened.

---

Summary

A quick “last backup times” check is one of the simplest, highest-value things you can run against a SQL Server instance. It catches most coverage problems early and takes one query to run.

Make it part of your baseline health checks, and treat any missing or unexpectedly old result as something to chase down immediately, not something to note and move past. Recovery is only possible before you find out the hard way, never after.