Notification for missing database backups

I got a question today about sending notifications from SQL Server when there are databases which have not been backed up in the past 24 hours.

The solution is simple – schedule an SQL Server Agent job which uses Database Mail to send emails if there are missing backups. All you have to do in the following script is to replace the ‘Enter_Mail_Profile_Here’ and ‘Enter_Recepient_Email_Here’ values for the sp_send_dbmail procedure parameters.

What this job does is, it sends an email to the recipient with a list of all databases which have not had a full backup in the past 24 hours of the execution of this job.

Read through the code below and make sure you have Database Mail setup before you run this SQL Server Agent job. Also, you will have to set a schedule for the job according to your taste.

 

?Download download.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
END
 
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Check for missing backups',
                @enabled=1,
                @notify_level_eventlog=0,
                @notify_level_email=0,
                @notify_level_netsend=0,
                @notify_level_page=0,
                @delete_level=0,
                @description=N'No description available.',
                @category_name=N'[Uncategorized (Local)]',
                @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CheckForMissingBackups1',
                @step_id=1,
                @cmdexec_success_code=0,
                @on_success_action=1,
                @on_success_step_id=0,
                @on_fail_action=2,
                @on_fail_step_id=0,
                @retry_attempts=0,
                @retry_interval=0,
                @os_run_priority=0, @subsystem=N'TSQL',
                @command=N'declare @ServerName varchar(250)
declare @DatabaseList varchar(4000)
declare @CountMissingBackups int
declare @MailSubject varchar(250)
declare @MailText varchar (4000)
 
;WITH CTE as
(
SELECT
   CONVERT(CHAR(100), SERVERPROPERTY(''Servername'')) AS [Server],
   msdb.dbo.backupset.database_name
FROM    msdb.dbo.backupset
WHERE     msdb.dbo.backupset.type = ''D''
GROUP BY msdb.dbo.backupset.database_name
HAVING      (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE()))
 
UNION
 
--Databases without any backup history
SELECT    
   CONVERT(CHAR(100), SERVERPROPERTY(''Servername'')) AS [Server],
   master.dbo.sysdatabases.NAME AS database_name
FROM
   master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset
       ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name
WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> ''tempdb''
--ORDER BY msdb.dbo.backupset.database_name
)
select @CountMissingBackups = count(*), @ServerName = [Server] ,@Databaselist = COALESCE(@DatabaseList + '', '', '''') + CAST(database_name AS varchar(250))
from CTE
group by [Server], database_name
 
 
-- send the mail
 
if @CountMissingBackups > 0
begin
 
set @MailSubject = ''Backups on ''+@ServerName
set @MailText = ''The following databases have not been backed up in the past 24 hours: ''+@Databaselist
 
EXEC sp_send_dbmail @profile_name=''Enter_Mail_Profile_Here'',
@recipients=''Enter_Recepient_Email_Here'',
@subject=@MailSubject,
@body=@MailText
 
end
   ',
                @database_name=N'msdb',
                @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
 
GO

 

Comments are closed.