SQL SERVER – Get Last Restore Date

I report Restore Date from the latest retained msdb history row. My earlier query returned every matching entry.

SELECT d.name AS DatabaseName,d.create_date,d.compatibility_level,
       rh.restore_date,rh.restore_history_id,rh.restore_type
FROM sys.databases d
OUTER APPLY (SELECT TOP (1) r.restore_date,r.restore_history_id,r.restore_type
 FROM msdb.dbo.restorehistory r WHERE r.destination_database_name=d.name
 ORDER BY r.restore_date DESC,r.restore_history_id DESC) rh
ORDER BY d.name;
-- Separate, approved history retention action only:
-- EXEC msdb.dbo.sp_delete_database_backuphistory @database_name=N'YourDatabase';

OUTER APPLY retains databases with no recorded restore as NULL history. Date and history ID define the latest row. It can represent full, differential, log or another restore type. It is not necessarily final recovery or backup creation.

History can be purged. It can also describe an earlier database with the same destination name. Correlate it with the actual recovery evidence.

The commented cleanup procedure is a separate retention operation. It deletes backup and restore metadata, rather than physical backup files. Preserve incident and recovery evidence before cleanup.

Reference: Restore-history scope.

Related reading

• SQL in Sixty Seconds series
• MAX Columns Ever Existed in Table – SQL in Sixty Seconds #182
• Tuning Query Cost 100% – SQL in Sixty Seconds #181
• Queries Using Specific Index – SQL in Sixty Seconds #180
• Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
• One Scan for 3 Count Sum – SQL in Sixty Seconds #178
• SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176

Retained restore history is not immutable recovery evidence, it is instance metadata with retention and name-reuse limits.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.