last db bakup | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

last db bakup

where do i find last db backup?
could you tell me which field?

Use msdb..backupset to look for the last backup. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Backup Start Time – backup_start_date
Backup End Time – backup_finish_date
Backup Type – type
Name of Database – database_name Refer BOL for more details… Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

thank you i figured this out:
select a.name,
bakupFile=m.physical_device_name
from master.dbo.sysdatabases a , msdb.dbo.backupset o,
msdb.dbo.backupmediafamily m,
(SELECT database_name,
MAX(backup_finish_date) as ‘maxfinishdate’
FROM msdb.dbo.backupset
WHERE Type = ‘D’
GROUP BY database_name) C
where a.name=o.database_name
and o.media_set_id = m.media_set_id AND
o.backup_finish_date = C.maxfinishdate AND
o.type = ‘D’ and a.name not in (‘master’,’tempdb’,’msdb’,’model’)
Already you have database_name, server_name & machine_name fields on BACKUPSET table and why do you want to join other system table, its unnecessary overhead. Always limit queries against system tables to avoid any issues.
HTH
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com

]]>