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/ and http://sqlmag.com/sql-server/dba-repository-2010

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'

ELSE 'Unknown'

END,

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)