Automating the monitoring of backup completion

[lupidus]

Hello all,
I just wanted to get some feedback regarding methods to automate the verification full backups are taking place daily for all databases on server(s).

I plan on writing a script that will execute the following:

select name, dbid, status, version from master.dbo.sysdatabases where name like N'%' -- get list of dbs

select * from msdb.dbo.backupset where database_name='<db_name>' and type='D' order by backup_start_date

By doing the above I believe I will get the newest full backup for each database on a server. This date can then be compared to the current date. If the two are not equal then notification will be sent to an operator, etc.

Is this a valid approach? Any other suggestions?

Thanks

 

[mrdenny]

Is something like this what you are looking for?

use master
select sysdatabases.name, 
	max(backup_finish_date), 
	case when datediff(dd, max(backup_finish_date), getdate()) > 1 or max(backup_finish_date) is null then 
		'Not backed up' 
	else 
		null 
	end
from sysdatabases
left outer join msdb..backupset backupset on sysdatabases.name = backupset.database_name
	and type = 'D' 
group by sysdatabases.name
[code]

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible.  All it takes is a little research. (Me)
[noevil]
[URL unfurl="true"]http://www.mrdenny.com[/URL]