Backup Verification
The following script runs against the current server and retrieves number of days since last full or differential and the number of days between them. it also retrieves the number of hours since the last transaction log backup.
It currently returns a grid output.
currently the script is under going changes to send XML back to an email that SQL monitors and will be inserted into a dashboard type webpage.
By setting parameters for how long it should be between each type of backup tracked the dashboard turns the server red if the threshold is missed.
/* returns in one row number of days/hours since last full/diff or transactional backup for each db on the server *//* also returns data on user dbs not backed up */
declare @Server varchar(40)
set @server = CONVERT(varchar(35), SERVERPROPERTY('machinename'))+'\'+isnull(CONVERT(varchar(35), SERVERPROPERTY('instancename')),'DEFAULT')
--full backups
SELECT @server,
fullrec.database_name,
datediff(dd,fullrec.backup_finish_date,getdate()) as 'FullDays',
fullrec.backup_finish_date as 'FullFinish',
datediff(dd,diffrec.backup_finish_date,getdate()) as 'DiffDays',
diffrec.backup_finish_date as 'DiffFinish',
Case
when diffrec.backup_finish_date is NULL then NULL
else datediff(dd,fullrec.backup_finish_date,diffrec.backup_finish_date)
end as 'DaysBetwix',
datediff(hh,tranrec.backup_finish_date,getdate()) as 'TranHours',
tranrec.backup_finish_date as 'TranFinish'
FROM msdb..backupset as fullrec
left outer join msdb..backupset as tranrec
on tranrec.database_name = fullrec.database_name
and tranrec.type = 'L'
and tranrec.backup_finish_date =
((select max(backup_finish_date)
from msdb..backupset b2
where b2.database_name = fullrec.database_name
and b2.type = 'L'))
left outer join msdb..backupset as diffrec
on diffrec.database_name = fullrec.database_name
and diffrec.type = 'I'
and diffrec.backup_finish_date =
((select max(backup_finish_date)
from msdb..backupset b2
where b2.database_name = fullrec.database_name
and b2.type = 'I'))
where fullrec.type = 'D' -- full backups only
and fullrec.backup_finish_date =
(select max(backup_finish_date)
from msdb..backupset b2
where b2.database_name = fullrec.database_name
and b2.type = 'D')
and fullrec.database_name in (select name from master..sysdatabases)
and fullrec.database_name not in ('tempdb','pubs','northwind','model')
-- never backed up
Union all
select @server
,name --, '**** ','Red',
,9999,'1900-01-01 00:00:00',
NULL, NULL , NULL, NULL, NULL
from master..sysdatabases as record
where name not in (select distinct database_name from msdb..backupset)
and name not in ('tempdb','pubs','northwind','model')
order by 1,2 
