How to see restore history of a deleted database?

Question

For example, I had a database and I want to see which physical backup file was used to restore into it. The problem is that the database was deleted. How can I see the record?

asked 2019-11-23 by sql-noob

Answer

Depending on how the database was dropped, your luck will vary considerably.

If the database was dropped simply by using the DROP DATABASE command, then the restore history will be available to you.

However, if the database was dropped via the SSMS GUI after the restore, then you may be out of luck. In SSMS, the default is to select the option for “Delete backup and restore history information for databases”. When this option is selected, the backup & restore history for the dropped database(s) will be removed from msdb:

However, if you either uncheck that box, or do the drop from T-SQL, then your backup & restore history will be available.

CREATE DATABASE Floop;
BACKUP DATABASE Floop TO DISK = 'C:\temp\Floop.bak' WITH INIT;
RESTORE DATABASE Floop FROM DISK = 'C:\temp\Floop.bak' WITH REPLACE;
DROP DATABASE Floop;

To see the restore history, simply query msdb.dbo.restorehistory:

SELECT *
FROM msdb.dbo.restorehistory
WHERE destination_database_name = 'Floop';

The details of the restorehistory table are available in the docs. Essentially, it contains the database that was restored, information about who restored it, the type of restore, and the restore point.

If you want information about the backup that was restored, you can join to other backup tables. When a database from a different instance is restore, SQL Server copies the backup information from the backup header in the file into the backup tables.

SELECT 
        RestoreDate = rh.restore_date,
        RestoredDb  = rh.destination_database_name,
        RestoreUser = rh.[user_name],
        RestoreType = rh.restore_type,
        ReplaceDb   = rh.[replace],
        RecoverDb   = rh.[recovery],
        BackupSourceServer  = bh.machine_name,
        BackupSourceDb      = bh.database_name,
        BackupFinishDate    = bh.backup_finish_date,
        BackupFile          = bmf.physical_device_name
FROM msdb.dbo.restorehistory    AS rh
JOIN msdb.dbo.backupset         AS bh  ON bh.backup_set_id = rh.backup_set_id
JOIN msdb.dbo.backupmediafamily AS bmf ON bmf.media_set_id = bh.media_set_id
WHERE rh.destination_database_name = 'Floop';

answered 2019-11-23 by Andy Mallon