find out SQL Server DB restored with which backup file

Find the backup from which database is restored in SQL Server
When you have your backup file, you can tell whether it is a full/differential/log backup but querying the .bak file. You can even tell whether backup is fine or not by simple query

 

USE MASTER
Restore verifyonly from disk='D:\sample.bak'
Go
but after DB is restored and you have to find with what backup that particular DB is restore then you have to run the following query to find out....
Use Master
GO
SELECT [Msrh].[destination_database_name],
[Msrh].[restore_date],
[Msbs].[backup_start_date],
[Msbs].[backup_finish_date],
[Msbs].[database_name] as [source_database_name],
[Msbmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory Msrh
INNER JOIN msdb..backupset Msbs
ON [Msrh].[backup_set_id] = [Msbs].[backup_set_id]
INNER JOIN msdb..backupmediafamily Msbmf
ON [Msbs].[media_set_id] = [Msbmf].[media_set_id]
ORDER BY [Msrh].[restore_date] DESC
GO
Hope the above is useful

Revert for issues and more information or doubts

Thanks