How to get the date of the last backup in SQL

I would like to get the date of the last database backup using SQL - any ideas?If you don't have many tables and you want to keep the added objects added
after the backup, then you can restore the backup to a different database,
then import the data. If you have many tables and not too many objects
added, then it's better to script your added objects, restored the db from
the backup, and added the objects again.
"John Austin" <John.Austin@.ManagedNewsgroups.com> wrote in message
news:96C2AEE9-6F5A-4B38-9018-77BE1B28D34F@.microsoft.com...
> I would like to get the date of the last database backup using SQL - any
ideas?|||I think this is stored in the MSDB database, poke around and you'll find
some backup history tables.
Mike Kruchten
"John Austin" <John.Austin@.ManagedNewsgroups.com> wrote in message
news:96C2AEE9-6F5A-4B38-9018-77BE1B28D34F@.microsoft.com...
> I would like to get the date of the last database backup using SQL - any
ideas?|||I think we are at cross purposes here - I just want to be able to get the calendar date that the database was last backed up. You can see this value in Enterprise Manager by right clicking a database and choosing properties "Last database backup: 12/12/2003 12:01". How can I get this information vis Transact SQL? It must be in some table somewhere.|||RESTORE HEADERONLY, see Books Online for detail.
"John Austin" <John.Austin@.ManagedNewsgroups.com> wrote in message
news:78845ADD-4C1B-411C-BE2E-BDFC145FD4F1@.microsoft.com...
> I think we are at cross purposes here - I just want to be able to get the
calendar date that the database was last backed up. You can see this value
in Enterprise Manager by right clicking a database and choosing properties
"Last database backup: 12/12/2003 12:01". How can I get this information vis
Transact SQL? It must be in some table somewhere.|||Or if you are not looking at the file itself but rather in the database:
use msdb
select max(sjh.run_date) from sysjobhistory sjh
inner join sysjobs sj on sj.job_id = sjh.job_id
inner join sysdbmaintplan_jobs smj on smj.job_id = sj.job_id
inner join sysdbmaintplans sm on sm.plan_id = smj.plan_id
inner join sysdbmaintplan_databases smb on smb.plan_id = sm.plan_id
inner join sysdbmaintplan_history smh on smh.plan_id = sm.plan_id
where sjh.run_status = 1
and smb.database_name = 'yourDBname'
and smh.activity = 'backup database'
"John Austin" <John.Austin@.ManagedNewsgroups.com> wrote in message
news:78845ADD-4C1B-411C-BE2E-BDFC145FD4F1@.microsoft.com...
> I think we are at cross purposes here - I just want to be able to get the
calendar date that the database was last backed up. You can see this value
in Enterprise Manager by right clicking a database and choosing properties
"Last database backup: 12/12/2003 12:01". How can I get this information vis
Transact SQL? It must be in some table somewhere.|||Thanks, Mik
Your suggestion gets me what I asked for, the date that the database was last backed up. Quentin's response gets me what I needed next, the creation date of an actual backup file to make sure that it is the right one. Both very useful, Thanks.|||Hi,
Please use the below query from QA,
select top 1 database_name,backup_start_date ,backup_finish_date
from msdb..backupset
where database_name='pubs'
order by backup_finish_date desc
Thanks
Hari
MCDBA
"Mike Kruchten" <mkruchten@.fsisolutions.com> wrote in message
news:uJsfwU33DHA.2404@.TK2MSFTNGP12.phx.gbl...
> I think this is stored in the MSDB database, poke around and you'll find
> some backup history tables.
> Mike Kruchten
>
> "John Austin" <John.Austin@.ManagedNewsgroups.com> wrote in message
> news:96C2AEE9-6F5A-4B38-9018-77BE1B28D34F@.microsoft.com...
> > I would like to get the date of the last database backup using SQL - any
> ideas?
>|||Thanks, Hari