DBA Scripts: Get Last Restore History

July 19, 2026

–

Part of the DBA-Tools Project.

A Backup You’ve Never Restored Is a Guess, Not a Plan

Every DBA can point to a backup schedule. Far fewer can point to proof that any of those backups actually restore. A backup job finishing green tells you the file was written, not that it’s usable, not that the process works end to end, and not that anyone on the team actually knows how to run it under pressure. The only way to know a restore genuinely works is to have run one, and msdb.dbo.restorehistory is the honest record of whether that’s ever happened.

Get-LastRestoreHistory pulls the most recent restore per database: when it happened, from which backup and how old that backup was at the time, who ran it, and whether it went in WITH RECOVERY or left for further log restores. It’s the script that answers “when did we last prove this actually works” instead of “when did the backup job last succeed”.

Why Last Restore History Matters

A green backup job is not a tested restore. The two are unrelated failure modes: a backup can succeed for months while the restore path is broken (wrong permissions, missing files, a corrupted backup set) and nobody finds out until a real incident.
backup_age_at_restore_days tells you how stale the tested backup was, which matters for judging whether your actual RPO has ever been validated, not just assumed.
user_name shows who’s actually run a restore. If it’s always the same one person, that’s a bus-factor problem worth knowing about before it becomes urgent.
An empty result set is itself the finding. No rows means no restore has ever been recorded on this instance, and that’s the exact gap this script exists to surface.

When to Run This Script

As part of a DR/backup-strategy audit, to check the restore side of the story, not just the backup side
After any restore test or drill, to confirm it actually landed in msdb.dbo.restorehistory the way you expect
When inheriting a server, to find out whether restore testing has ever actually happened here
Before signing off on an RPO/RTO number to anyone outside the team, since an untested restore is a promise, not a fact

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-LastRestoreHistory
Category    : backups
Purpose     : Full restore history from msdb — when each database was last restored, from which backup, and by whom. Use to verify DR restore tests have actually been run.
Author      : Peter Whyte (https://sqldba.blog)
Requires    : msdb access (db_datareader on msdb or sysadmin)
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

/* ── Most recent restore per database ────────────────────────────────────── */
;WITH ranked AS (
    SELECT
        rh.restore_history_id,
        rh.destination_database_name,
        rh.restore_date,
        DATEDIFF(DAY, rh.restore_date, GETDATE())           AS days_since_restore,
        CASE rh.restore_type
            WHEN 'D' THEN 'Full'
            WHEN 'I' THEN 'Differential'
            WHEN 'L' THEN 'Log'
            WHEN 'F' THEN 'File'
            WHEN 'P' THEN 'Page'
            WHEN 'R' THEN 'Revert'
            ELSE rh.restore_type
        END                                                 AS restore_type,
        bs.database_name                                    AS source_database,
        bs.backup_finish_date                               AS backup_taken_date,
        DATEDIFF(DAY, bs.backup_finish_date, rh.restore_date) AS backup_age_at_restore_days,
        rh.user_name,
        rh.recovery                                         AS with_recovery,
        rh.replace                                          AS with_replace,
        ROW_NUMBER() OVER (
            PARTITION BY rh.destination_database_name
            ORDER BY rh.restore_date DESC
        )                                                   AS rn
    FROM msdb.dbo.restorehistory rh
    LEFT JOIN msdb.dbo.backupset bs ON bs.backup_set_id = rh.backup_set_id
)
SELECT
    destination_database_name  AS database_name,
    restore_date,
    days_since_restore,
    restore_type,
    source_database,
    backup_taken_date,
    backup_age_at_restore_days,
    user_name,
    with_recovery,
    with_replace
FROM ranked
WHERE rn = 1
ORDER BY restore_date DESC;

Ranks every row in msdb.dbo.restorehistory per destination database by restore date, joins back to msdb.dbo.backupset to find the source database and how old the backup was, and returns only the most recent restore per database, newest first.

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

# Most recent restore per database, from msdb history:
.\run.ps1 Get-LastRestoreHistory

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

This script lives in the repo at:

Example Output

Get-LastRestoreHistory output showing key columns, SQL Server output

Real output captured 2026-07-31 07:21 against ..

Understanding the Results

The honest version of this post’s Example Output: before the row above existed, this exact query returned zero rows on this instance. This lab box has been backing up databases throughout this project (see Get Database Backup History) but had never had a single restore performed against it. The row shown was generated for this post by backing up DBAMonitor and restoring it under a new name (DBAMonitor_RestoreDemo), a real restore, not staged data, then the temporary restored copy was dropped afterward. The restorehistory row itself stays in msdb permanently regardless.

That’s not a contrived example. It’s the exact gap this script is built to catch: an instance can run backups reliably for months while restore capability has never once been verified. If you run this script against a real server and get zero rows, that’s the finding, not a script problem.

Reading the columns that matter most:

restore_date / days_since_restore — how long ago the last verified restore happened. Anything measured in months, on a database that matters, is worth flagging.
backup_age_at_restore_days — how stale the backup being restored was. A restore test against a week-old backup tells you less about your actual RPO than one against last night’s.
with_recovery — True means the database came fully online (a complete point-in-time test). False means it was left NORECOVERY, mid-sequence for further log restores, worth knowing which mode your last test actually exercised.
user_name — who ran it. Useful both for accountability and for spotting a single-person dependency.

Best Practices

Treat “backup succeeded” and “restore verified” as two separate, equally necessary facts. This script only answers the second one.
Schedule restore tests deliberately (monthly or quarterly, depending on how critical the database is) rather than relying on them happening incidentally.
Restore to a differently-named or isolated target when testing, the way this post’s own example does, so a routine test never risks the production database it’s meant to protect.
If this script comes back empty for a database you consider critical, that’s the finding to escalate, not a script limitation to work around.

Related Scripts

You may also find these scripts useful:

Summary

A backup strategy is only half-verified until the restore half has actually been tested, and msdb.dbo.restorehistory is the only honest record of whether that’s happened. Get-LastRestoreHistory turns that record into a one-glance answer per database: when, from what, how stale, and by whom.

If it comes back empty for something you’d call critical, that’s not a script quirk to shrug off. It’s the same gap this post’s own real output demonstrated: reliable backups and a verified restore path are two different things, and only one of them shows up in a green Agent job.

Backups Disaster Recovery

Blog Categories

Comments

Leave a Reply Cancel reply