Restore History

Overview

msdb has recorded every restore since the instance was built and nothing in this product has ever read it.

That is the largest blank spot in the backup story. The Backup Status page can say whether the backups are current, and only this table can say whether anybody has ever put one back.

The Restore History page: every restore msdb has recorded
The whole page with the window set to everything. Eight restores onto four databases, the last one 23 days ago, and two of those databases are no longer on this instance.

Where to find it

Expand a server in the tree, expand the msdb database, then MSDB → Restore History.

Four things that come out of nowhere else

Whether a restore has ever been done at all

An instance with years of backups and no restore in msdb is an instance where nobody has tested one, and that is worth one line on a page rather than a conversation after an outage.

Where the database came from

restorehistory keeps the backup set it was restored from, and the backup set keeps the server that took the backup.

A database whose source server is not this one arrived from somewhere else, which is the fact that explains an orphaned user, a missing job, and a compatibility level nobody set.

What overwrote what

A restore with REPLACE went over a database that was already there.

Where a log chain was broken

A restore in the middle of somebody else’s chain is why the log backups after it cannot be applied, and the timestamps are what line the two up.

Why it is drawn as lanes

Restores come in sequences rather than one at a time. A full, a differential and a run of logs are one recovery, and a lane shows them as one cluster where a sorted list shows them as seventeen rows.

  • One lane per database, one mark per restore.
  • A bar where the restore had measurable duration, a point where it did not.
  • The axis format is chosen from the window, which on this page can span a decade.

Reading the chart

The lanes: one per database, one mark per restore
Four lanes, two restores each, all inside one morning. A recovery made of a full and the restores that follow it is one cluster here rather than a run of rows.

One lane per database and one mark per restore, colored by what the restore was: green for a full, blue for the log and differential restores that follow it, amber for an overwrite or for a backup that came from another server.

The label under each name is the count, and it says when that database is no longer on this instance, which is a row only this table still has.

Reading the grid

The grid: what was restored, from where, and with which options
Backup taken on is the column with the finding in it: a server that is not this one means the database arrived from somewhere else. Options records the REPLACE and NORECOVERY as they were used.
Column What it is
Restored When the restore ran.
Database The database it was restored into.
Kind Database, file, filegroup, log, differential, or verify only.
By The login that ran it.
Backup taken on The server the backup came from. Not this one is the finding.
Of database The database the backup was of, which is often a different name.
Backup finished When the backup being restored was taken, which is the recovery point.
Backup size How large it was.
Options REPLACE, NORECOVERY, STANDBY, STOPAT and the rest, as they were used.
From The media the restore read: the file path, the tape, or the backup device.

The toolbar

Control Options Default
Window 30 days, a year, everything a year
Backup status Opens the Backup Status report.
Space and retention Opens msdb Space and Retention.

Where the data comes from

Source What it gives
dbo.restorehistory The restore itself: the destination database, the restore type, the user, the date, and the options used.
dbo.restorefile, dbo.restorefilegroup Which files were moved where.
dbo.backupset The backup behind it: the source server, the source database, the finish time and the size.
dbo.backupmediafamily The physical device the restore read from.

These rows are deleted by sp_delete_backuphistory along with the backup history, so a purge that took the backup ledger took this too. Backup Ledger is the page that says how far back the ledger goes.

Messages you may see

Nothing has ever been restored onto this instance. msdb has no restore history at all. Either no restore has been run here since the instance was built, or sp_delete_backuphistory has taken the records with it.

Nothing has been restored here in the last 30 days. With the count that is recorded in total, so a quiet month reads differently from an empty table.

Report Why you would go there
Backup Status Whether the backups you would restore from are current.
Backup Ledger Whether the history behind these rows is complete.
Recovery Exposure Where a broken chain would leave you, from the backup side.
Orphan Users The symptom of a database that arrived from another server.
msdb Space and Retention How far back the restore history goes.

Frequently asked questions

Has anybody ever tested a restore here? That is the first line of the page. An empty restore history on an instance with years of backups is the answer.

Why does the source database have a different name? Because the database was restored under a new name, which is what a refresh of a test environment looks like. The Of database column is the original.

A restore shows with NORECOVERY and nothing after it. That database was left in a restoring state, or the rest of the sequence happened after the history was purged.

We restore nightly to a test box. Can I hide those? Not from this page, but they will be one lane and one obvious rhythm, which is the shape the lane chart was chosen for.