--- Finding backups and Location
 
Declare @databasename as varchar(100)

set @databasename = 'AdventureWorksDW' 

Select a.database_name + rtrim(case when type ='L' then space(5)+ name
when type ='D' then space(1)+name
when type ='I' then space(3)+name else '' end)+ space(3)+
convert(varchar, Backup_Start_date, 121),b.physical_device_name
from msdb..backupset a inner join msdb..backupmediafamily b
on a.media_set_id = b.media_set_id 
where database_name = @databasename 
and Backup_Start_Date >= (select max(Backup_Start_Date)
from msdb..backupset
where database_name = @databasename
and type = 'D')
order by Backup_Start_date