Determine last backup?

If I back up a database without using a maintenance plan, is there a query
that can be made to determine when the last full backup of that database
occurred?
Thanks in advance!Try:
select top 1
backup_finish_date
from
msdb.dbo.backupset
where
database_name = 'Northwind'
and type = 'D'
order by
backup_finish_date desc
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Jim Norton" <jim.norton@.joe.com> wrote in message
news:%237SbYsn%23FHA.1312@.TK2MSFTNGP09.phx.gbl...
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
>|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102
) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '''
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102
) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '''
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !