Query to get all databases restore history

During a side-by-side database migration, mostly like you will backup all the user databases from the source SQL Server, copy the backup files to the destination server (which may be a newer version when you are doing an version upgrade), and finally restore all the databases on the destination SQL Server. Below query is handy for you to verify this migration by checking the latest restore history of all the databases in an instance.

;WITH CTE AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY destination_database_name ORDER BY restore_date DESC) AS row_num, * FROM msdb.dbo.restorehistory
) SELECT d.[name], CTE.restore_date, CTE.restore_type, BS.backup_start_date, BS.server_name, BMF.physical_device_name
FROM master.sys.databases AS D
LEFT JOIN CTE ON D.[name] = CTE.destination_database_name AND CTE.row_num = 1
LEFT JOIN msdb.dbo.backupset AS BS ON CTE.backup_set_id = BS.backup_set_id
LEFT JOIN msdb.dbo.backupmediafamily AS BMF ON BS.media_set_id = BMF.media_set_id
ORDER BY D.[name];

This query should be run on the destination SQL Server. It returns all databases including system databases and user databases, the date and time of their latest restore operation, type of restore operation (D = Full, I = Differential, L = Log), date and time of the backup being made, the source SQL Server name, and the physical name of the backup device (the backup file) being used. For example:

By checking the result of this query, you can make sure you got the right backup files to restore on the destination SQL Server.