1

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 = '[email protected]',
    @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 ;
4
  • I don't think the logic can happen in the order you expect. Do you actually have to send the results of the query as a file attachment? Why not just send the e-mail with the query in the BODY, and they can run the query manually? Or just build a body that has a list of the database names, and only send the mail if it's non-empty? What you're currently trying to do is like calling Geico to insure your car as you're flying off a cliff... Sep 11, 2014 at 21:36
  • Hi Aaron. I'm trying to make this as automated as possible. I don't mind having the results in the body of the email. Running the query manually is not an option. Once this is working I will add the statements into a job to run daily. Do you happen to have another way of sending email notices when a backup has not occurred within 24 hours? Sep 11, 2014 at 21:39
  • I get it, I'm not saying don't automate it, but consider: (1) they're not going to be able to do anything about anything without connecting to the server anyway (2) the e-mail should be enough of a trigger to get them to do something (3) if the e-mail comes every single night, they're not going to read the body anyway. Sep 11, 2014 at 21:54
  • I agree. I'm hoping the email doesn't come every night because the backups should be generated every day. This email is definitely a trigger for action so I only want it sent when the backups did not occur within 24 hours. Thanks again for answering my questions here on stackexchange. Sep 11, 2014 at 21:57

2 Answers 2

3

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 = '[email protected]',
  --  @subject = N'Databases not backed up within 24 hrs',
  --  @body = @dblist;
END
1

One approach I've used is to separate the querying and the emailing into different job steps. The first runs your query. It writes output to a file (Job step properties -> Advanced page -> Output file) using tokens to identify each day's file. The second step sends the email with the first step's output as an attachment.

The first step sets a "success" or "fail" condition to control whether the second step runs or not.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.