Path of the backup file

Hi All,
Can anyone tell me if there is any system table/view/SP available where I
can query to view the path of a database backup file when the last database
backup is taken?
For example I can find the backup information(like backup begin start date,
backup finish date, etc) of a database using msdb.dbo.backupset. I am
looking for something similar query where I can see the location, either to
tape, device, or any local directory, of the backup.
Thanks a lot,
VMTake a look at the backupmediafamily table in msdb. The
columns Physical_device_name and device_type tell you what
device and path was used for the backup.
-Sue
On Mon, 16 Jan 2006 11:38:02 -0800, "VM"
<VM@.discussions.microsoft.com> wrote:

>Hi All,
>Can anyone tell me if there is any system table/view/SP available where I
>can query to view the path of a database backup file when the last database
>backup is taken?
>For example I can find the backup information(like backup begin start date,
>backup finish date, etc) of a database using msdb.dbo.backupset. I am
>looking for something similar query where I can see the location, either to
>tape, device, or any local directory, of the backup.
>Thanks a lot,
>VM|||There is a child table of backupset called backupfile that contains the
information you need. Look up System tables in BOL. Choose All Databases
as the subtopic. Scroll down to the section called "Tables in the MSDB
database" for more details.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"VM" <VM@.discussions.microsoft.com> wrote in message
news:A0E40887-16A5-4128-8AEE-DE5955C79860@.microsoft.com...
> Hi All,
> Can anyone tell me if there is any system table/view/SP available where I
> can query to view the path of a database backup file when the last
> database
> backup is taken?
> For example I can find the backup information(like backup begin start
> date,
> backup finish date, etc) of a database using msdb.dbo.backupset. I am
> looking for something similar query where I can see the location, either
> to
> tape, device, or any local directory, of the backup.
> Thanks a lot,
> VM|||Thanks Sue! It worked.
VM
"Sue Hoegemeier" wrote:

> Take a look at the backupmediafamily table in msdb. The
> columns Physical_device_name and device_type tell you what
> device and path was used for the backup.
> -Sue
> On Mon, 16 Jan 2006 11:38:02 -0800, "VM"
> <VM@.discussions.microsoft.com> wrote:
>
>|||Thanks for Information Geoff. I was looking for backupmediafamily table.
VM
"Geoff N. Hiten" wrote:

> There is a child table of backupset called backupfile that contains the
> information you need. Look up System tables in BOL. Choose All Databases
> as the subtopic. Scroll down to the section called "Tables in the MSDB
> database" for more details.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "VM" <VM@.discussions.microsoft.com> wrote in message
> news:A0E40887-16A5-4128-8AEE-DE5955C79860@.microsoft.com...
>
>|||Sue was right with backupmediafamily. I should have looked at my backup
management code instead of writig from memory. Either way, the information
in BOL is very useful.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"VM" <VM@.discussions.microsoft.com> wrote in message
news:5E526962-6D6A-4827-8EAE-FBA04E9EF8CD@.microsoft.com...[vbcol=seagreen]
> Thanks for Information Geoff. I was looking for backupmediafamily table.
> VM
> "Geoff N. Hiten" wrote:
>