Agent Jobs

Get Notified If Your Backups Are Older Than One Day

Here’s some sql logic you can put in a Job and run it nightly. Basically it will check to see which databases haven’t had a recent backup. In this case it’s any database that doesn’t have a full database backup for the last day. It creates a list of those databases, and then shoots off an email to notify you.

Here’s what the email will look like:

Here’s a list of steps that this logic will carry out.

1. Check to see if SQL Database Mail has been configured.
2. Completely configures SQL Database Mail for you.
3. Sends off a test email so you know if it’s working or not. (Test email includes name of server/instance so you know where the email came from)
4. Gets a list of all databases that are missing a recent full database backup.
5. Finds the last 30 days backups, and their locations so you’ll know when and where the former backups were going.
6. Sends off a nifty formatted email showing you how old the last backup is, and pulls a list of the last 30 days backups for reference.

This Job will ignore Mirror configured databases. Principals will be checked for current backup, but if this Job was deployed to a Mirrored server the Mirror partner databases will simply be ignored as they don’t require backups. If they were switched to the Principal they of course would be checked.

use msdb;
set nocount on
set ansi_nulls on
set quoted_identifier on

----------------------------------------------------------------------
-- Configure SQL Database Mail if it's not already configured.
if (select top 1 name from msdb..sysmail_profile) is null
	begin
		----------------------------------------------------------------------
		-- Enable SQL Database Mail
		exec master..sp_configure 'show advanced options',1
		reconfigure;
		exec master..sp_configure 'database mail xps',1
		reconfigure;

		----------------------------------------------------------------------
		-- Add a profile
		execute msdb.dbo.sysmail_add_profile_sp
			@profile_name		= 'SQLDatabaseMailProfile'
		,	@description		= 'SQLDatabaseMail';

		----------------------------------------------------------------------
		-- Add the account names you want to appear in the email message.
		execute msdb.dbo.sysmail_add_account_sp
			@account_name		= 'sqldatabasemail@your_domain.com'
		,	@email_address		= 'sqldatabasemail@your_domain.com'
		,	@mailserver_name	= 'your_smtp_server.your_domain.com'  
		--,	@port			= ####  --optional
		--,	@enable_ssl		= 1 --optional
		--,	@username		='MySQLDatabaseMailProfile' --optional
		--,	@password		='MyPassword' --optional

		-- Adding the account to the profile
		execute msdb.dbo.sysmail_add_profileaccount_sp
			@profile_name		= 'SQLDatabaseMailProfile'
		,	@account_name		= 'sqldatabasemail@your_domain.com'
		,	@sequence_number	= 1;

		-- Give access to new database mail profile (DatabaseMailUserRole)
		execute msdb.dbo.sysmail_add_principalprofile_sp
			@profile_name		= 'SQLDatabaseMailProfile'
		,	@principal_id		= 0
		,	@is_default		= 1;

		----------------------------------------------------------------------
		-- Get Server info for test message

		declare	@get_basic_server_name				varchar(255)
		declare	@get_basic_server_name_and_instance_name		varchar(255)
		declare	@basic_test_subject_message			varchar(255)
		declare	@basic_test_body_message				varchar(max)
		set	@get_basic_server_name			= (select cast(serverproperty('servername') as varchar(255)))
		set	@get_basic_server_name_and_instance_name	= (select  replace(cast(serverproperty('servername') as varchar(255)), '\', '   SQL Instance: '))
		set	@basic_test_subject_message		= 'Test SMTP email from SQL Server: ' + @get_basic_server_name_and_instance_name
		set	@basic_test_body_message			= 'This is a test SMTP email from SQL Server:  ' + @get_basic_server_name_and_instance_name + char(10) + char(10) + 'If you see this.  It''s working perfectly :)'

		----------------------------------------------------------------------
		-- Send quick email to confirm email is properly working.

		EXEC msdb.dbo.sp_send_dbmail
			@profile_name	= 'SQLDatabaseMailProfile'
		,	@recipients	= 'SQLJobAlerts@your_domain.com'
		,	@subject		= @basic_test_subject_message
		,	@body		= @basic_test_body_message;

		-- Confirm message send
		-- select * from msdb..sysmail_allitems
	end

----------------------------------------------------------------------------------------
-- get basic server info.

declare	@server_name_basic		varchar(255)
declare	@server_name_instance_name	varchar(255)
declare	@server_time_zone			varchar(255)
set	@server_name_basic		= (select cast(serverproperty('servername') as varchar(255)))
set	@server_name_instance_name	= (select  replace(cast(serverproperty('servername') as varchar(255)), '\', '   SQL Instance: '))

----------------------------------------------------------------------------------------
-- set message subject.
declare	@message_subject		varchar(255)
set	@message_subject		= 'A current database backup is missing for:  ' + @server_name_instance_name

----------------------------------------------------------------------------------------
-- create table to hold database list without a recent full database backup
set nocount on
if object_id('tempdb..#missing_current_backup') is not null
	drop table #missing_current_backup

create table #missing_current_backup
(
	[server]		varchar(255)
,	[database]	varchar(255)
,	[time_of_backup]	varchar(255)
,	[last_backup]	varchar(255)
)

----------------------------------------------------------------------------------------
-- populate table with databases that have no recent full database backup
insert into #missing_current_backup ([server], [database],[time_of_backup],[last_backup])
select
	'server'		= upper(@@servername)
,	'database'	= upper(bs.database_name)
,	'time_of_backup'	= replace(replace(replace(left(max(bs.backup_finish_date), 19),':', '-'), 'AM', 'am'), 'PM', 'pm') + ' ' + datename(dw, max(bs.backup_finish_date))
,	'last_backup'	= cast (datediff(day, max(bs.backup_finish_date), getdate()) as varchar(10)) + ' Days Old'
from
	msdb.dbo.backupset bs join master.sys.database_mirroring sdm on bs.database_name = db_name(sdm.database_id)
where
	type = 'd'
	and bs.database_name in (select name from sys.databases)
	and sdm.mirroring_role_desc is null
	or sdm.mirroring_role_desc != 'mirror'
group by
	bs.database_name
having
	(max(bs.backup_finish_date) < dateadd(hour, -24, getdate()))

----------------------------------------------------------------------------------------
-- create table to hold list of the last known backups and their locations

if object_id('tempdb..#get_last_known_backups') is not null
	drop table #get_last_known_backups

create table #get_last_known_backups
(
	[database]	varchar(255)
,	[time_of_backup]	varchar(255)
,	[location]		varchar(255)
,	[backup_file]	varchar(255)
)

----------------------------------------------------------------------------------------
-- populate table with a list of the last known backups and their locations
insert into #get_last_known_backups ([database], [time_of_backup], [location], [backup_file])
select
	'database'	= upper(bs.database_name)
,	'time_of_backup'	= replace(replace(replace(left(max(bs.backup_finish_date), 19),':', '-'), 'AM', 'am'), 'PM', 'pm') + ' ' + datename(dw, max(bs.backup_finish_date))
,	'location'		= reverse(right(reverse(upper(bmf.physical_device_name)), len(bmf.physical_device_name) - charindex('\',reverse(bmf.physical_device_name),1) + 1))
,	'backup_file'	= right(bmf.physical_device_name, charindex('\', reverse('\' + bmf.physical_device_name)) - 1)
from
	msdb.dbo.backupset bs join msdb.dbo.backupmediafamily bmf on bs.media_set_id = bmf.media_set_id
where
	bs.database_name in (select [database] from #missing_current_backup)
	and bs.backup_finish_date > (select getdate()- 30)
group by
	bs.database_name, bs.backup_finish_date, bmf.physical_device_name
order by
	bs.database_name, bs.backup_finish_date desc

----------------------------------------------------------------------------------------
-- create conditions for html tables in top and mid sections of email.

declare @xml_top			NVARCHAR(MAX)
declare @xml_mid			NVARCHAR(MAX)
declare @body_top			NVARCHAR(MAX)
declare @body_mid			NVARCHAR(MAX)

----------------------------------------------------------------------------------------
-- set xml top table td's
-- create html table object for: #agent_job_step_error_report
set @xml_top = 
	cast(
		(select 
			[server]		as 'td'
		,	''
		,	[database]	as 'td'
		,	''
		,	[time_of_backup]	as 'td'
		,	''
		,	[last_backup]	as 'td'
		,	''
		from  #missing_current_backup 
		order by [database] asc 
		for xml path('tr')
	,	elements)
	as NVARCHAR(MAX)
		)

----------------------------------------------------------------------------------------
-- set xml mid table td's
-- create html table object for: #agent_job_information
set @xml_mid = 
	cast(
		(select 
			[database]	as 'td'
		,	''
		,	[time_of_backup]	as 'td'
		,	''
		,	[location]		as 'td'
		,	''
		,	[backup_file]	as 'td'

		from  #get_last_known_backups 
		order by [database], [time_of_backup] desc 
		for xml path('tr')
	,	elements)
	as NVARCHAR(MAX)
		)

----------------------------------------------------------------------------------------
-- format email
set @body_top =
		'<html>
		<head>
			<style>
					h1{
						font-family: sans-serif;
						font-size: 110%;
					}
					h3{
						font-family: sans-serif;
						color: black;
					}

					table, td, tr, th {
						font-family: sans-serif;
						border: 1px solid black;
						border-collapse: collapse;
					}
					th {
						text-align: left;
						background-color: gray;
						color: white;
						padding: 5px;
					}

					td {
						padding: 5px;
					}
			</style>
		</head>
		<body>
		<H3>' + @message_subject + '</H3>
		<h1>Note: Last known backup info</h1>
		<table border = 1>
		<tr>
			<th> Server Name	</th>
			<th> Database	</th>
			<th> Time Of Backup	</th>
			<th> Last Backup	</th>
		</tr>'
		
set @body_top = @body_top + @xml_top + '</table>

<h1>Last 30 days backups and their locations</h1>

<table border = 1>
		<tr>
			<th> Database	</th>
			<th> Time Of Backup	</th>
			<th> Location	</th>
			<th> Backup File	</th>
		</tr>'		
		
+ @xml_mid + '</table>
		<h1>Go to the server by pasting in the following text under: Start-Run, or (Win + R)</h1>
		<h1>mstsc -v:' + @server_name_basic + '</h1>'
+ '</body></html>'

----------------------------------------------------------------------------------------
-- send email.


if exists(select top 1 server from #missing_current_backup)
	begin
		EXEC msdb.dbo.sp_send_dbmail
			@profile_name		= 'SQLDatabaseMailProfile'
		,	@recipients			= 'SQLJobAlerts@your_domain.com'
		,	@subject			= @message_subject
		,	@body				= @body_top
		,	@body_format		= 'HTML';

	end

drop table #missing_current_backup
drop table #get_last_known_backups




Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s