Have a look at this: http://ola.hallengren.com/
This is also great: http://www.sqlservercentral.com/articles/Backup+%2f+Restore/66564/
In the end I ended up impleneting my own solution after being inspired by http://sql-ution.com/download-the-dba-repository/
This is the query I use to check for backups from previous day:
SELECT @@ServerName as Server_Name,
d.name as Database_Name
FROM master..sysdatabases d
LEFT JOIN msdb..backupset b
ON d.name = b.database_name
AND b.backup_start_date = (SELECT MAX(backup_start_date)
FROM msdb..backupset b2
WHERE b.database_name = b2.database_name
AND b2.type IN ('D','I'))
WHERE d.name != 'tempdb'
--AND d.name NOT IN (SELECT db_name FROM dbautility.dbo.db_exclude)
AND DATABASEPROPERTYEX(d.name, 'Status') = 'ONLINE'
GROUP BY d.name, b.type, b.backup_size
HAVING MAX(ISNULL(DATEDIFF(dd,ISNULL(b.backup_start_date, '01/01/1900'),GETDATE()),0)) > 1
I have an SSIS package which collects this from 25 instances every morning:
and just about the time I get my coffee ready at 9AM I get a report telling me which databases have not been backed up the day before:
I've also got this report for the SQL Agent jobs:
And this is the code I use:
SELECT JobServerName = @@ServerName
, JobID = j.job_id
, JobName = j.name
, JobOwner = ISNULL(l.name, 'Unknown')
, JobRunDate = CAST(STR(jh.run_date, 8, 0) + ' ' + LTRIM(STUFF(STUFF(RIGHT(REPLICATE('0', 6)
+ LTRIM(STR(jh.run_time, 6, 0)), 6),
3, 0, ':'), 6, 0, ':')) AS datetime)
, JobStepName = isnull(js.step_name, 'Undefined')
, JobStepSubsystem = isnull(js.subsystem, 'Undefined')
, JobStepResultDescription = CASE jh.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In Progress'
STUFF(STUFF(REPLACE(STR(run_duration,6),' ','0'),5,0,':'),3,0,':') as 'Duration'
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.sys.server_principals AS l ON j.owner_sid = l.[sid]
LEFT JOIN msdb.dbo.sysjobhistory AS jh ON j.job_id = jh.job_id
LEFT JOIN msdb.dbo.sysjobsteps AS js ON jh.step_id = js.step_id
AND jh.job_id = js.job_id
WHERE 1 = 1
and not (jh.run_date is not null and js.step_name is null)
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda. David Edwards - Media lens
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.Howard Zinn