If this helps, I install the following procedure on all SQL Server instances I set up and create an agent job which runs the proc each morning to tell me what databases have not been backed up for a day, or not backed up at all.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[BackupCheckNotification] Script Date: 12/11/2014 16:36:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BackupCheckNotification]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @data AS TABLE
(
ID INT IDENTITY(1, 1),
DBName NVARCHAR(128),
LastBackupDate DATETIME
)
DECLARE @BackupsNotDone AS TABLE
(
ID INT IDENTITY(1, 1),
DBName NVARCHAR(128),
LastBackupDate DATETIME
)
DECLARE @mailBody NVARCHAR(MAX);
DECLARE @mailSubject NVARCHAR(100);
DECLARE @cnt INT,
@count INT;
DECLARE @DataRecord NVARCHAR(500);
SET @cnt = 0;
SET @count = 1;
SET @mailSubject = 'Full Database Backups Failing on Server: '
+ @@SERVERNAME;
SET @mailBody = 'The following database(s) running on ' + @@servername
+ ' have not been backed for over a day. <p>'
INSERT INTO @data
SELECT database_name,
COALESCE(CONVERT(VARCHAR(25), MAX(backup_finish_date), 113),
'Backup Not Taken')
FROM msdb.dbo.backupset
WHERE type = 'D'
AND database_name IN ( SELECT name
FROM sys.databases
WHERE state_desc = 'ONLINE' )
GROUP BY database_name
UNION
SELECT name,
'1900-01-01 00:00:00.000' AS 'Backup Not Taken'
FROM sys.databases
WHERE name NOT IN ( SELECT database_name
FROM msdb.dbo.backupset
WHERE type = 'D'
GROUP BY database_name )
AND name != 'TempDB'
INSERT INTO @BackupsNotDone
SELECT DBName,
LastBackupDate
FROM @data
WHERE LastBackupDate < DATEADD(DAY, -1, GETDATE())
SELECT @cnt = COUNT(*)
FROM @BackupsNotDone;
IF @cnt > 0
BEGIN
WHILE @count <= @cnt
BEGIN
SET @DataRecord = '';
DECLARE @backupDate VARCHAR(25);
SET @backupDate = '';
SELECT @backupDate = CASE LastBackupDate
WHEN '1900-01-01 00:00:00.000'
THEN 'Never Backed Up'
ELSE CONVERT(NVARCHAR(25), LastBackupDate, 113)
END
FROM @BackupsNotDone
WHERE ID = @count;
SELECT @DataRecord = '<b>' + DBName
+ ' </b> - Last Back Up Date: ' + @backupDate
FROM @BackupsNotDone
WHERE ID = @count;
SET @mailBody = @mailBody + '
' + @DataRecord;
SET @count = @count + 1;
END
-- Email out the notification:
EXEC msdb.dbo.sp_send_dbmail @recipients = N'mail@mail.com', -- change to valid mail address
@subject = @mailSubject, @body = @mailBody,
@profile_name = 'DefaultMailProfile', -- change to valid mail profile
@body_format = HTML;
END
END
Produces nice output emails like the following:
"The following database(s) running on ServerOne\Corp have not been backed for over a day.
master - Last Back Up Date: 07 Nov 2014 22:51:38:000
model - Last Back Up Date: 07 Nov 2014 22:51:38:000
msdb - Last Back Up Date: Never Backed Up"