I have been asked many times on how to find the backup file location for a specific database, to be honest I always forget the name of the table I need to join to “backupset” and end up looking for the table name on the MSDB database to find the backup file location. I think most of you are familiar with the “backupset” table in MSDB, if not check the msdn article about it, but in order to find the location of the backup file another MSDB table is needed, the table is “backupmediafamily” I will recommend you to check the msdn article about this table. In order to avoid checking msdb the next time someone ask me about a backup location I decided to post a script in my blog and I hope that the script can be useful for others as well:
use MSDB
go
SELECT
bs.[database_name]
,bs.[backup_start_date]
,bs.[type]
,bs.[server_name]
,bmf.physical_device_name
FROM [msdb].[dbo].[backupset] bs inner join [msdb].[dbo].[backupmediafamily] bmf
on bs.media_set_id = bmf.media_set_id
where
bs.[database_name] = 'the name of the database from which you are searching the backup file'
/*
Source: http://msdn.microsoft.com/en-us/library/ms186299.aspx
Backup type. Can be:
D = Database
I = Differential database
L = Log
F = File or filegroup
G =Differential file
P = Partial
Q = Differential partial
Can be NULL.
*/
and type = 'D'
order by backup_start_date desc
IgnacioSalom.com 