Backup notifier

I adapted a query I found online to check for databases that have not been backed up

I only wanted the script to run when there was a problem, so that it did not spam my inbox.  This script only sends an email if backups are missing in the past 24 hours.

Here is the site where I found the original script:  See the section for Databases Missing a Data (aka Full) Back-Up Within Past 24 Hours

I modified it to check the results, and send an email if there was more than one record.

if OBJECT_ID('tempdb..#backup_success') is not null
DROP TABLE #backup_success

CREATE TABLE #backup_success
[Server] CHAR(100),
database_name sysname,
last_db_backup_date datetime,
backup_age int


DECLARE @Check_Query nvarchar(MAX)
SET @Check_Query = '

--Databases Missing a Data (aka Full) Back-Up Within Past 24 Hours
--Databases with data backup over 24 hours old
CONVERT(CHAR(100), SERVERPROPERTY(''Servername'')) AS Server,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date,
DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [Backup Age (Hours)]
FROM msdb.dbo.backupset
WHERE msdb.dbo.backupset.type = ''D''
and database_name in(
select [name]
from master.dbo.sysdatabases
GROUP BY msdb.dbo.backupset.database_name
HAVING MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE())


--Databases without any backup history
CONVERT(CHAR(100), SERVERPROPERTY(''Servername'')) AS Server,
master.dbo.sysdatabases.NAME AS database_name,
NULL AS [Last Data Backup Date],
9999 AS [Backup Age (Hours)]
master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset
ON = msdb.dbo.backupset.database_name
WHERE msdb.dbo.backupset.database_name IS NULL AND <> ''tempdb''

INSERT #backup_success
exec sp_executesql @Check_Query

DECLARE @row_count int
SELECT @row_count = COUNT(*) from #backup_success

IF @row_count > 0

exec msdb..sp_send_dbmail @profile_name = 'DBA_Profile',
@recipients = '',
@subject = 'Missing Backups',
@query_result_header = 0,
@query = @Check_Query


Test it out in development. Try creating a new database, and it should email you that a database has not been backed up. Schedule it as a job, and hopefully you’ll do less work checking backups. If the actual notification job fails, then an email is sent out that the job fails. This is done in the job properties under Notifications.

Technically the experts recommend restoring every database in order to test the restores. I’ve never worked anywhere that had a server like that sitting around. But that is something to think about. You don’t know for sure that a backup works until you restore it.

This entry was posted in Database Administration and tagged . Bookmark the permalink.

Leave a Reply

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

You are commenting using your 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