SQL Server – Send Email if No Backups in 24 Hours

sql server

I'm trying to combine the script from Tim Ford (http://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/)

------------------------------------------------------------------------------------------- 
--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'  
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

with the sp_send_dbmail proc. Right now it is sending emails even when 0 rows are returned (i.e. all databases have been backed up within last 24 hrs).

What I would like to have happen is an email sent out ONLY when the recordCount is > 0. i.e. there is a database that has not been backed up within 24hrs.

Current script which sends an email even when there are 0 results returned. 

EXEC msdb.dbo.sp_send_dbmail
    --@profile_name = 'SQLDBEmail',
    @recipients = 'dba@someemailaddress.com',
    @query = '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''  
            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',
    @subject = 'Databases not backed up within 24 hrs',
    @attach_query_result_as_file = 1 ;

Best Answer

Instead of e-mailing the results of the query as a file attachment (which, SQL Server won't know it's empty until after it runs the query as a part of sending the e-mail), I suggest instead building up the body of the e-mail as follows, and then using a simple IF condition to only send the e-mail if at least one database has contributed to populating the string:

DECLARE 
  @dblist NVARCHAR(MAX) = N'', 
  @c CHAR(2) = CHAR(13) + CHAR(10), @t CHAR(1) = CHAR(9),
  @s SYSNAME = @@SERVERNAME;

SELECT @dblist += @c + @c + @s + @c + @t + db + @c + @t
  + COALESCE(CONVERT(CHAR(10), d, 120) + ' ' + CONVERT(CHAR(8), d, 108), 'NULL')
  + @c + @t + CONVERT(VARCHAR(11), age)
FROM
(
  SELECT 
   bs.database_name AS db, 
   MAX(bs.backup_finish_date) AS d, 
   DATEDIFF(HOUR, MAX(bs.backup_finish_date), GETDATE()) AS age 
-------------^^^^ please don't use lazy shorthand like hh
  FROM    msdb.dbo.backupset AS bs 
  WHERE [type] = 'D'  
  GROUP BY database_name 
  HAVING (MAX(backup_finish_date) < DATEADD(HOUR, -24, GETDATE()))  

UNION ALL -- why UNION? By definition there are no duplicates to filter.
          -- in fact you could re-write this without two separate queries at all.

  SELECT
    name AS db,  
    NULL AS d,  
    9999 AS age 
  FROM 
    master.sys.databases AS d
------ don't use sysdatabases - old and deprecated
  WHERE name <> N'tempdb' AND NOT EXISTS 
  (
    SELECT 1 FROM msdb.dbo.backupset 
      WHERE database_name = d.name
  )
) AS x
ORDER BY db;

IF @dblist > N''
BEGIN
  PRINT @dblist;
  --EXEC msdb.dbo.sp_send_dbmail 
  --  @recipients = 'dba@someemailaddress.com',
  --  @subject = N'Databases not backed up within 24 hrs',
  --  @body = @dblist;
END
Related Question