How to find the backup file location?


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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s