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: http://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/.  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
)

SET NOCOUNT ON

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

SET NOCOUNT ON
-------------------------------------------------------------------------------------------
--Databases Missing a Data (aka Full) Back-Up Within Past 24 Hours
-------------------------------------------------------------------------------------------
--Databases with data backup over 24 hours old
SELECT
CONVERT(CHAR(100), SERVERPROPERTY(''Servername'')) AS Server,
msdb.dbo.backupset.database_name,
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())


UNION

--Databases without any backup history
SELECT
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)]
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
'

INSERT #backup_success
exec sp_executesql @Check_Query

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

IF @row_count > 0
BEGIN

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

END

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.

Advertisements
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:

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