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.

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:
[vbcol=seagreen]
> 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:

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 automat
ed
> fashion. Is there a way to determine the pathname and filename of the late
st
> 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:
[vbcol=seagreen]
> 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:
>

Find the Data Bases which can not be Backed up are some resons

hi all.,
How can I find the Data Base which can't backed up for reasons
like Data Base is in the middle of the Restore before
i start Backup Process .
Regards.,
Sri
This probably isn't the best method to use but via Enterprise Manager
under Management you can view the maintenance plan history for the
backup plan and look for the red X by the database name that indicates
a failure for the operation against that database.
You might try selecting directly from the
msdb.dbo.sysdbmaintplan_history table if your plan logs to the
database.
Otherwise there should be log files in the LOG directory.
HTH -- Mark D Powell --
|||Hii Mark.,
Thanks for ur reply .
Can I Get the DataBase which couldn't be backuped using executing
Query or procedure .
I am new to Db management , hence pardon me if any mistakes .
Regards.,
Sridhar
Mark D Powell wrote:
> This probably isn't the best method to use but via Enterprise Manager
> under Management you can view the maintenance plan history for the
> backup plan and look for the red X by the database name that indicates
> a failure for the operation against that database.
> You might try selecting directly from the
> msdb.dbo.sysdbmaintplan_history table if your plan logs to the
> database.
> Otherwise there should be log files in the LOG directory.
> HTH -- Mark D Powell --

Find the Data Bases which can not be Backed up are some resons

hi all.,
How can I find the Data Base which can't backed up for reasons
like Data Base is in the middle of the Restore before
i start Backup Process .
Regards.,
SriThis probably isn't the best method to use but via Enterprise Manager
under Management you can view the maintenance plan history for the
backup plan and look for the red X by the database name that indicates
a failure for the operation against that database.
You might try selecting directly from the
msdb.dbo.sysdbmaintplan_history table if your plan logs to the
database.
Otherwise there should be log files in the LOG directory.
HTH -- Mark D Powell --|||Hii Mark.,
Thanks for ur reply .
Can I Get the DataBase which couldn't be backuped using executing
Query or procedure .
I am new to Db management , hence pardon me if any mistakes .
Regards.,
Sridhar
Mark D Powell wrote:
> This probably isn't the best method to use but via Enterprise Manager
> under Management you can view the maintenance plan history for the
> backup plan and look for the red X by the database name that indicates
> a failure for the operation against that database.
> You might try selecting directly from the
> msdb.dbo.sysdbmaintplan_history table if your plan logs to the
> database.
> Otherwise there should be log files in the LOG directory.
> HTH -- Mark D Powell --

Find the Data Bases which can not be Backed up are some resons

hi all.,
How can I find the Data Base which can't backed up for reasons
like Data Base is in the middle of the Restore before
i start Backup Process .
Regards.,
SriThis probably isn't the best method to use but via Enterprise Manager
under Management you can view the maintenance plan history for the
backup plan and look for the red X by the database name that indicates
a failure for the operation against that database.
You might try selecting directly from the
msdb.dbo.sysdbmaintplan_history table if your plan logs to the
database.
Otherwise there should be log files in the LOG directory.
HTH -- Mark D Powell --|||Hii Mark.,
Thanks for ur reply .
Can I Get the DataBase which couldn't be backuped using executing
Query or procedure .
I am new to Db management , hence pardon me if any mistakes .
Regards.,
Sridhar
Mark D Powell wrote:
> This probably isn't the best method to use but via Enterprise Manager
> under Management you can view the maintenance plan history for the
> backup plan and look for the red X by the database name that indicates
> a failure for the operation against that database.
> You might try selecting directly from the
> msdb.dbo.sysdbmaintplan_history table if your plan logs to the
> database.
> Otherwise there should be log files in the LOG directory.
> HTH -- Mark D Powell --sql

Find the Data Bases which can not be Backed up are some resons

hi all.,
How can I find the Data Base which can't backed up for reasons
like Data Base is in the middle of the Restore before
i start Backup Process .

Regards.,

SriThis probably isn't the best method to use but via Enterprise Manager
under Management you can view the maintenance plan history for the
backup plan and look for the red X by the database name that indicates
a failure for the operation against that database.

You might try selecting directly from the
msdb.dbo.sysdbmaintplan_history table if your plan logs to the
database.

Otherwise there should be log files in the LOG directory.

HTH -- Mark D Powell --|||Hii Mark.,

Thanks for ur reply .

Can I Get the DataBase which couldn't be backuped using executing
Query or procedure .

I am new to Db management , hence pardon me if any mistakes .

Regards.,

Sridhar

Mark D Powell wrote:
> This probably isn't the best method to use but via Enterprise Manager
> under Management you can view the maintenance plan history for the
> backup plan and look for the red X by the database name that indicates
> a failure for the operation against that database.
> You might try selecting directly from the
> msdb.dbo.sysdbmaintplan_history table if your plan logs to the
> database.
> Otherwise there should be log files in the LOG directory.
> HTH -- Mark D Powell --