Finding latest full backup file name

We are instituting a procedure to restore the latest full backup (*.BAK
extension) of our production database to a training database in an automated
fashion. Is there a way to determine the pathname and filename of the latest
full backup of a database (from msdb perhaps). or do I need to use DOS Dir
command manipulation to get this information from file timestamps? I would
then use T-SQL to restore the filename over the existing training database.
Any ideas?
--
Larry Menzin
American Techsystems Corp.If you are not using multiple devices, may be this can help.
use msdb
go
select top 1
a.[name],
a.[database_name],
a.type,
a.backup_start_date,
a.backup_finish_date,
c.physical_device_name
from
dbo.backupset as a
inner join
dbo.backupmediaset as b
on a.media_set_id = b.media_set_id
inner join
dbo.backupmediafamily as c
on b.media_set_id = c.media_set_id
where
a.[database_name] = 'northwind'
and a.type = 'D'
order by
a.backup_finish_date desc
AMB
"Larry Menzin" wrote:
> We are instituting a procedure to restore the latest full backup (*.BAK
> extension) of our production database to a training database in an automated
> fashion. Is there a way to determine the pathname and filename of the latest
> full backup of a database (from msdb perhaps). or do I need to use DOS Dir
> command manipulation to get this information from file timestamps? I would
> then use T-SQL to restore the filename over the existing training database.
> Any ideas?
> --
> Larry Menzin
> American Techsystems Corp.|||Just what I was looking for. Thanks a lot.
--
Larry Menzin
American Techsystems Corp.
"Alejandro Mesa" wrote:
> If you are not using multiple devices, may be this can help.
> use msdb
> go
> select top 1
> a.[name],
> a.[database_name],
> a.type,
> a.backup_start_date,
> a.backup_finish_date,
> c.physical_device_name
> from
> dbo.backupset as a
> inner join
> dbo.backupmediaset as b
> on a.media_set_id = b.media_set_id
> inner join
> dbo.backupmediafamily as c
> on b.media_set_id = c.media_set_id
> where
> a.[database_name] = 'northwind'
> and a.type = 'D'
> order by
> a.backup_finish_date desc
>
> AMB
>
> "Larry Menzin" wrote:
> > We are instituting a procedure to restore the latest full backup (*.BAK
> > extension) of our production database to a training database in an automated
> > fashion. Is there a way to determine the pathname and filename of the latest
> > full backup of a database (from msdb perhaps). or do I need to use DOS Dir
> > command manipulation to get this information from file timestamps? I would
> > then use T-SQL to restore the filename over the existing training database.
> > Any ideas?
> >
> > --
> > Larry Menzin
> > American Techsystems Corp.