Today I needed to get the file name for the latest full back. I am using it so I can restore a database using an SSIS packages onto another server. Since there are full, diffs and logs, I had to specify type “D” in the code. That sounds a little off, but L = Log, I = Diff and D = Full. I debating on putting the subquery into a CTE, but this is such a quick hit, I did it the dirty way. I hope it helps you!

SELECT physical_device_name,
backup_start_date,
backup_finish_date,
backup_size/1024.0 AS BackupSizeKB
FROM msdb.dbo.backupset bINNER JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = 'MyDatabase' AND type = 'D' AND backup_start_date = (SELECT MAX(backup_start_date) FROM dbo.backupset WHERE database_name = 'MyDatabase' AND type = 'D')
ORDER BY backup_finish_date DESC