Get last fully restored databases info

last_restored.sql

WITH cte AS
(
	SELECT
		RH.destination_database_name, RH.restore_type
	,	ROW_NUMBER() OVER (PARTITION BY RH.destination_database_name, RH.restore_type ORDER BY RH.restore_history_id DESC) AS rn
	,	RH.restore_date, BS.backup_start_date, BS.backup_finish_date
	,	CASE WHEN CONVERT(varchar, DATEDIFF(ms, backup_start_date, backup_finish_date) / 1000 / 86400) > 0 THEN CONVERT(varchar, DATEDIFF(ms, backup_start_date, backup_finish_date) / 1000 / 86400) + ' day(s) ' ELSE '' END + CONVERT(varchar, DATEADD(ms, DATEDIFF(ms, backup_start_date, backup_finish_date), 0), 114) AS elapsed_time
	,	FORMAT(BS.backup_size / 1073741824., 'N2') AS backup_size_gb
	,	FORMAT(BS.compressed_backup_size / 1073741824., 'N2') AS compressed_backup_size_gb
	FROM	msdb.dbo.restorehistory AS RH
	JOIN	msdb.dbo.backupset AS BS
	  ON	RH.backup_set_id = bs.backup_set_id
)
SELECT	*
FROM	cte AS BS1
WHERE	BS1.restore_type = 'D' AND BS1.rn = 1 -- most recent FULL backupset