Where'd that backup go?

A friend IM'd me today and mentioned that he was having an issue with a backup. It appeared that the backup ran, but he was unable to find the backup file. I have a 'quickie' backup history script that I use for telling me when stuff was backed up, but it doesn't mention where it was backed up to, since there's some MAX() and GROUP BY going on, and using individual file names would obviate that.

Here's the 'When' version:

SELECT
  SERVERPROPERTY('ServerName') AS ServerName,
  a.Name AS DBName,
  databasepropertyex(a.Name, 'Recovery'),
  CASE b.type
    WHEN 'L' THEN 'Tranlog'
    WHEN 'D' THEN 'Full'
    WHEN 'I' THEN 'Differential'
  END AS BackupType,
  b.[User_Name],
  MAX(b.Backup_Finish_Date) AS LastBackup
FROM Master.dbo.sysdatabases a
INNER JOIN msdb.dbo.backupset b
  ON a.Name = b.Database_Name
WHERE b.Backup_Finish_Date > (DATEADD(dd,-14,getdate()))
GROUP BY a.Name, b.Type, b.[User_Name]
ORDER BY a.Name ASC, LastBackup DESC

Using that he saw that the backup did indeed run, and was successful. But where was that backup file at?

This found it:

SELECT
  a.Name as DBName,
  CASE b.type
    WHEN 'L' THEN 'Tranlog'
    WHEN 'D' THEN 'Full'
    WHEN 'I' THEN 'Differential'
  END AS BackupType,
  b.[User_Name],
  b.Backup_Finish_Date,
  f.physical_device_name
FROM Master.dbo.sysdatabases a
INNER JOIN msdb.dbo.backupset b
  ON a.Name = b.Database_Name
INNER JOIN msdb.dbo.backupmediafamily f
  ON b.media_set_id = f.media_set_id
WHERE b.backup_finish_date > getdate() - 7
  AND a.Name = 'MyDB' -- Replace with your DB name.   
ORDER BY b.backup_finish_date DESC  

Enjoy.

-D.