To Know the Backup set used to restore a SQL Server database

SELECT a.restore_date ,

a.destination_database_name ,

a.user_name ,

b.server_name ,

c.physical_device_name

FROM restorehistory a

INNER JOIN Backupset b ON a.backup_set_id = b.backup_set_id

INNER JOIN backupmediafamily c ON b.media_set_id = c.media_set_id

WHERE destination_database_name = 'databasename'