Problem Statement:

Today, one of my colleague was looking for a way to identify the backup file path for one his restored databases. The below script would help you in similar situation.

Script:

DECLARE @dbname sysname, @days int
SET @dbname = NULL --provide database name you want
--number of days since need to check, script will default to 25
SET @days = -25 
SELECT
     rs.destination_database_name AS [Database],
     rs.user_name AS [Restored By],
     CASE WHEN rs.restore_type = 'D' THEN 'Database'
      WHEN rs.restore_type = 'F' THEN 'File'
      WHEN rs.restore_type = 'G' THEN 'Filegroup'
      WHEN rs.restore_type = 'I' THEN 'Differential'
      WHEN rs.restore_type = 'L' THEN 'Log'
      WHEN rs.restore_type = 'V' THEN 'Verifyonly'
      WHEN rs.restore_type = 'R' THEN 'Revert'
     ELSE rs.restore_type 
     END AS [Restore Type],
     rs.restore_date AS [Restore Started],
     bmf.physical_device_name AS [Restored From], 
     rf.destination_phys_name AS [Restored To]
FROM msdb.dbo.restorehistory rs
 INNER JOIN msdb.dbo.backupset bs 
     ON rs.backup_set_id = bs.backup_set_id
 INNER JOIN msdb.dbo.restorefile rf 
     ON rs.restore_history_id = rf.restore_history_id
 INNER JOIN msdb.dbo.backupmediafamily bmf 
     ON bmf.media_set_id = bs.media_set_id
WHERE rs.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) 
     AND destination_database_name = ISNULL(@dbname, destination_database_name) 
ORDER BY rs.restore_history_id DESC