locked
Need help to set Database backup alerts RRS feed

  • Question

  • Hello Guys ...Hope you alll are doing great !!

    I have requirment to automate the alerts for sql server database backups via DBmail for internal daily checks, please share the script or process to automate this requirement.

    For example : A SP which will trigger mail , if Database hasn't backed up since these many days.

    Please let me know if this is possible or else anyother alternative for same.

    Let me know incase of any query.

    your help will be highly appriciated.

    Thanks & Regards,


    Asandeen

    Thursday, October 13, 2016 11:47 AM

Answers

All replies

  • Hello,

    There is no Trigger available which fires when "nothing happens"

    Create a SQL Server-Agent Job which runs daily and queries backupset (Transact-SQL) to see, which databases has been backuped and which one not.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, October 13, 2016 11:51 AM
  • http://www.sqlmatters.com/Articles/Checking%20when%20a%20database%20was%20last%20backed%20up.aspx

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, October 13, 2016 11:56 AM
    Answerer
  • Please check below powershell ways to do it: Various ways given to customize your solution!

    https://blogs.technet.microsoft.com/heyscriptingguy/2011/05/02/use-powershell-to-report-sql-server-backup-status/

    In my case, I have some critical servers to check backup status for per day and send alerts separate to DBA and to stakeholder to be alerted for, it does below things:[This is one time data pull from separate servers on one central server and from there I used to send to all, using linked server]

    1. Data collection from all servers for backup done for the last 24 hours, by filtering different backup file types as I have more than one backup tool used and their backup file naming convention is different.

    2. Send email to all with count for system, user db, total db, and then backup count for system, and user db and total backup and finally total failure count too.

    3. Send email to DBA for server and database name, so that they can take backup on time.

    Code goes here:-

    -------------------------------------------------------

    Data collection from various servers:

    --REFRESHING DATABASES NAMES FOR BELOW SERVERS
    TRUNCATE TABLE MSDB.DBO.CENTRALIZE_BACKUP_MAIN_ALL

    --LOADING DATA

    INSERT INTO MSDB.DBO.CENTRALIZE_BACKUP_MAIN_ALL select 'SERVER1' AS 'SERVERNAME',NAME FROM [SERVER1].[MASTER].[DBO].[SYSDATABASES] where NAME <> 'tempdb'
    INSERT INTO MSDB.DBO.CENTRALIZE_BACKUP_MAIN_ALL select 'SERVER2' AS 'SERVERNAME', NAME FROM [SERVER2].[MASTER].[DBO].[SYSDATABASES] where NAME not in ('tempdb')
    INSERT INTO MSDB.DBO.CENTRALIZE_BACKUP_MAIN_ALL select 'SERVER3' AS 'SERVERNAME', NAME FROM [SERVER3].[MASTER].[DBO].[SYSDATABASES] where NAME <> 'tempdb'
    INSERT INTO MSDB.DBO.CENTRALIZE_BACKUP_MAIN_ALL select 'SERVER4' AS 'SERVERNAME', NAME FROM [SERVER4].[MASTER].[DBO].[SYSDATABASES] where NAME <> 'tempdb'
    INSERT INTO MSDB.DBO.CENTRALIZE_BACKUP_MAIN_ALL select 'SERVER5' AS 'SERVERNAME', NAME FROM [SERVER5].[MASTER].[DBO].[SYSDATABASES] where NAME <> 'tempdb'

    --REFRESHES DAILY BACKUP REPORT TABLE FOR ALL SERVERS
    TRUNCATE TABLE MSDB.DBO.CENTRALIZE_BACKUP_MAIN_ALL_DAILY

    --Backup Data Load for SERVER1
    INSERT INTO MSDB.DBO.CENTRALIZE_BACKUP_MAIN_ALL_DAILY
    SELECT   
      'SERVER1' as 'ServerName',
      bs.database_name as 'DatabaseName',
      bs.backup_start_date as 'BackupStartdate',
      bs.backup_finish_date as 'BackupFinishDate',
      (CASE bs.type
       WHEN 'D' THEN 'Database'
       WHEN 'L' THEN 'Log'
       END ) as 'BackupType',
      cast(round(bs.backup_size/1024/1024,2)as int) as 'BackupSize',
      bf.physical_device_name as 'BackupDeviceName'
      FROM [SERVER1].[msdb].[dbo].[backupmediafamily] bf
      INNER JOIN [SERVER1].[msdb].[dbo].[backupset] bs ON bf.media_set_id = bs.media_set_id
      WHERE (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - 1)
      ORDER BY
      bs.database_name, 
      bs.backup_finish_date

    --Backup Data Load for SERVER2

    INSERT INTO MSDB.DBO.CENTRALIZE_BACKUP_MAIN_ALL_DAILY
    SELECT   
      'SERVER2' as 'ServerName',
      bs.database_name as 'DatabaseName',
      bs.backup_start_date as 'BackupStartdate',
      bs.backup_finish_date as 'BackupFinishDate',
      (CASE bs.type
       WHEN 'D' THEN 'Database'
       WHEN 'L' THEN 'Log'
       END ) as 'BackupType',
      cast(round(bs.backup_size/1024/1024,2)as int) as 'BackupSize',
      bf.physical_device_name as 'BackupDeviceName'
      FROM [SERVER2].[msdb].[dbo].[backupmediafamily] bf
      INNER JOIN [SERVER2].[msdb].[dbo].[backupset] bs ON bf.media_set_id = bs.media_set_id
      WHERE (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - 1)
      ORDER BY
      bs.database_name, 
      bs.backup_finish_date


    --Backup Data Load for SERVER3

    INSERT INTO MSDB.DBO.CENTRALIZE_BACKUP_MAIN_ALL_DAILY
    SELECT   
      'SERVER3' as 'ServerName',
      bs.database_name as 'DatabaseName',
      bs.backup_start_date as 'BackupStartdate',
      bs.backup_finish_date as 'BackupFinishDate',
      (CASE bs.type
       WHEN 'D' THEN 'Database'
       WHEN 'L' THEN 'Log'
       END ) as 'BackupType',
      cast(round(bs.backup_size/1024/1024,2)as int) as 'BackupSize',
      bf.physical_device_name as 'BackupDeviceName'
      FROM [SERVER3].[msdb].[dbo].[backupmediafamily] bf
      INNER JOIN [SERVER3].[msdb].[dbo].[backupset] bs ON bf.media_set_id = bs.media_set_id
      WHERE (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - 1) 
      ORDER BY
      bs.database_name, 
      bs.backup_finish_date

    --System DB and Others Backup
    INSERT INTO MSDB.DBO.CENTRALIZE_BACKUP_MAIN_ALL_DAILY
    SELECT   
      'SERVER3' as 'ServerName',
      bs.database_name as 'DatabaseName',
      bs.backup_start_date as 'BackupStartdate',
      bs.backup_finish_date as 'BackupFinishDate',
      (CASE bs.type
       WHEN 'D' THEN 'Database'
       WHEN 'L' THEN 'Log'
       END ) as 'BackupType',
      cast(round(bs.backup_size/1024/1024,2)as int) as 'BackupSize',
      bf.physical_device_name as 'BackupDeviceName'
      FROM [SERVER3].[msdb].[dbo].[backupmediafamily] bf
      INNER JOIN [SERVER3].[msdb].[dbo].[backupset] bs ON bf.media_set_id = bs.media_set_id
      WHERE (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - 1) 
      ORDER BY
      bs.database_name, 
      bs.backup_finish_date

    --Backup Data Load for SERVER4

    INSERT INTO MSDB.DBO.CENTRALIZE_BACKUP_MAIN_ALL_DAILY
    SELECT   
      'SERVER4' as 'ServerName',
      bs.database_name as 'DatabaseName',
      bs.backup_start_date as 'BackupStartdate',
      bs.backup_finish_date as 'BackupFinishDate',
      (CASE bs.type
       WHEN 'D' THEN 'Database'
       WHEN 'L' THEN 'Log'
       END ) as 'BackupType',
      cast(round(bs.backup_size/1024/1024,2)as int) as 'BackupSize',
      bf.physical_device_name as 'BackupDeviceName'
      FROM [SERVER4].[msdb].[dbo].[backupmediafamily] bf
      INNER JOIN [SERVER4].[msdb].[dbo].[backupset] bs ON bf.media_set_id = bs.media_set_id
      WHERE (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - 1) AND bf.physical_device_name like '%.bak%'
      ORDER BY
      bs.database_name, 
      bs.backup_finish_date

    --Backup Data Load for SERVER5

    INSERT INTO MSDB.DBO.CENTRALIZE_BACKUP_MAIN_ALL_DAILY
    SELECT   
      'SERVER5' as 'ServerName',
      bs.database_name as 'DatabaseName',
      bs.backup_start_date as 'BackupStartdate',
      bs.backup_finish_date as 'BackupFinishDate',
      (CASE bs.type
       WHEN 'D' THEN 'Database'
       WHEN 'L' THEN 'Log'
       END ) as 'BackupType',
      cast(round(bs.backup_size/1024/1024,2)as int) as 'BackupSize',
      bf.physical_device_name as 'BackupDeviceName'
      FROM [SERVER5].[msdb].[dbo].[backupmediafamily] bf
      INNER JOIN [SERVER5].[msdb].[dbo].[backupset] bs ON bf.media_set_id = bs.media_set_id
      WHERE (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - 1) AND bf.physical_device_name like '%{%'
      ORDER BY
      bs.database_name, 
      bs.backup_finish_date


    --TRUNCATE TABLE [MSDB].[dbo].[CENTRALIZE_BACKUP_MAIN_ALL_COUNT_DAILY] FOR DAILY COUNT
    TRUNCATE TABLE [MSDB].[dbo].[CENTRALIZE_BACKUP_MAIN_ALL_COUNT_DAILY]
    --Updating Daily count table for DB Count
    INSERT INTO [MSDB].[dbo].[CENTRALIZE_BACKUP_MAIN_ALL_COUNT_DAILY]
    SELECT
    DISTINCT
    SERVERNAME,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL
    FROM MSDB.DBO.CENTRALIZE_BACKUP_MAIN_ALL

    --UPDATING COUNT FOR ALL DATABABSES FOR MASTER DB

    ---UPDATING COUNTS FOR STANDARD DB COUNTS
    ----------------------------------------------
    --Updatwe for TotalDBCount
    UPDATE  a
    SET a.TotalDBCount = b.count
    from [MSDB].[dbo].[CENTRALIZE_BACKUP_MAIN_ALL_COUNT_DAILY] a
    inner join
    (
    SELECT servername, 'TotalDBCount' as 'CountT',COUNT(*) as 'Count' FROM MSDB.DBO.CENTRALIZE_BACKUP_MAIN_ALL group by servername
    ) b on b.servername = a.servername
    where b.countt = 'TotalDBCount'

    --Updatwe for SystemDBCount
    UPDATE  a
    SET a.SystemDBCount = b.count
    from [MSDB].[dbo].[CENTRALIZE_BACKUP_MAIN_ALL_COUNT_DAILY] a
    inner join
    (
    SELECT servername, 'SystemDBCount' as 'CountT',COUNT(*) as 'Count' FROM MSDB.DBO.CENTRALIZE_BACKUP_MAIN_ALL WHERE NAME IN ('MASTER','MSDB','MODEL','TEMPDB') group by servername
    ) b on b.servername = a.servername
    where b.countt ='SystemDBCount'

    --Updatwe for UserDBCount
    UPDATE  a
    SET a.UserDBCount =b.count
    from [MSDB].[dbo].[CENTRALIZE_BACKUP_MAIN_ALL_COUNT_DAILY] a
    inner join
    (
    SELECT servername, 'UserDBCount' as 'CountT',COUNT(*) as 'Count' FROM MSDB.DBO.CENTRALIZE_BACKUP_MAIN_ALL WHERE NAME NOT IN ('MASTER','MSDB','MODEL','TEMPDB') group by servername
    ) b on b.servername = a.servername
    where b.countt = 'UserDBCount'


    --UPDATING COUNT FOR ALL DATABABSES FOR MASTER DB COMPLETED

    --------------------

    --UPDATING COUNT FOR ALL DATABABSES FOR DAILY DB STARTED


    --Updatwe for TotalDBCount
    UPDATE  a
    SET a.TotalDBBackupCount = b.count
    from [MSDB].[dbo].[CENTRALIZE_BACKUP_MAIN_ALL_COUNT_DAILY] a
    inner join
    (
    SELECT servername, 'TotalDBBackupCount' as 'CountT',COUNT(*) as 'Count' FROM MSDB.DBO.CENTRALIZE_BACKUP_MAIN_ALL_DAILY group by servername
    ) b on b.servername = a.servername
    where b.countt = 'TotalDBBackupCount'


    --Updatwe for SystemDBCount
    UPDATE  a
    SET a.TotalDBSystemDBBackupCount = b.count
    from [MSDB].[dbo].[CENTRALIZE_BACKUP_MAIN_ALL_COUNT_DAILY] a
    inner join
    (
    SELECT servername, 'TotalDBSystemDBBackupCount' as 'CountT',COUNT(*) as 'Count' FROM MSDB.DBO.CENTRALIZE_BACKUP_MAIN_ALL_DAILY WHERE DATABASENAME IN ('MASTER','MSDB','MODEL','TEMPDB') group by servername
    ) b on b.servername = a.servername
    where b.countt ='TotalDBSystemDBBackupCount'

    --Updatwe for UserDBCount
    UPDATE  a
    SET a.TotalUserDBBackupCount =b.count
    from [MSDB].[dbo].[CENTRALIZE_BACKUP_MAIN_ALL_COUNT_DAILY] a
    inner join
    (
    SELECT servername, 'TotalUserDBBackupCount' as 'CountT',COUNT(*) as 'Count' FROM MSDB.DBO.CENTRALIZE_BACKUP_MAIN_ALL_DAILY WHERE DATABASENAME NOT IN ('MASTER','MSDB','MODEL','TEMPDB') group by servername
    ) b on b.servername = a.servername
    where b.countt = 'TotalUserDBBackupCount'

    --Update for Failed DB count


    UPDATE  a
    SET a.[TotalFailedDBBackupCount] = (CASE WHEN (b.TotalDBBackupCount>b.TotalDBCount) THEN 0 WHEN b.TotalDBBackupCount =0 THEN b.TotalDBCount ELSE b.TotalDBCount - b.TotalDBBackupCount END )-- FROM [MSDB].[dbo].[CENTRALIZE_BACKUP_MAIN_ALL_COUNT_DAILY])
    from [MSDB].[dbo].[CENTRALIZE_BACKUP_MAIN_ALL_COUNT_DAILY] a
    inner join
    (
    SELECT servername, TotalDBCount as 'TotalDBCount', TotalDBBackupCount as 'TotalDBBackupCount' FROM [MSDB].[dbo].[CENTRALIZE_BACKUP_MAIN_ALL_COUNT_DAILY]  group by servername, TotalDBCount, TotalDBBackupCount
    ) b on b.servername = a.servername

    ---Backup Daily load completed

    select * from [MSDB].[dbo].[CENTRALIZE_BACKUP_MAIN_ALL_COUNT_DAILY]

    ------------------------------------------------------------------------------------------------------

    Code to send email to all stakehoder:

    -----------------------------------------------------


    SET QUOTED_IDENTIFIER ON
    SET NOCOUNT ON

    USE MSDB
    GO

    DECLARE @Body NVARCHAR(MAX),
        @TableHead VARCHAR(1000),
        @TableHead1 VARCHAR(1000),  
        @TableHead2 VARCHAR(1000),
        @TableTail VARCHAR(1000)

    SET @TableTail = '</table></body></html>' ;
    SET @TableHead = '<html><head>' + '<style>'
        + 'td {border: solid black;border-width: 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font: 15px arial} '
        + '</style>' + '</head>' + '<body>' + '<Centre><Bold>SQL DBA Supported Backup Status Alert for Production DB Servers</Center>'+'<BR><BR><Bold>Report generated on : '
        + CONVERT(VARCHAR(50), GETDATE(), 106) +'::'+ CONVERT(VARCHAR(50), GETDATE(), 108)
        + '</Bold><br><br><br> <table cellpadding=0 cellspacing=0 border=0>'
     + '<tr>' 
        + '<td bgcolor=#E6E6FA><b>Server Name</b></td>'
        + '<td bgcolor=#E6E6FA><b>Total DB Count</b></td>'
        + '<td bgcolor=#E6E6FA><b>Total System DB Count</b></td>'
        + '<td bgcolor=#E6E6FA><b>Total User DB Count</b></td>'  
        + '<td bgcolor=#E6E6FA><b>Total DB Backup Count</b></td>'
        + '<td bgcolor=#E6E6FA><b>Total System DB Backup Count</b></td>'
        + '<td bgcolor=#E6E6FA><b>Total User DB Backup Count</b></td>' 
        + '<td bgcolor=#E6E6FA><b>Total Failed DB Backup Count</b></td>'
        +'</tr>' ;

    SET @Body = (

    select
    TD = servername, '',
    TD = isnull([TotalDBCount],0),'',
    TD = isnull([SystemDBCount],0),'',
    TD = isnull([UserDBCount],0),'',
    TD = isnull([TotalDBBackupCount],0), '',
    TD = isnull([TotalDBSystemDBBackupCount],0),'',
    TD = isnull([TotalUserDBBackupCount],0),'',
    TD = isnull([TotalFailedDBBackupCount],0),''
    from [MSDB].[dbo].[CENTRALIZE_BACKUP_MAIN_ALL_COUNT_DAILY]
    order by servername

                     FOR   XML RAW('tr'),
                      ELEMENTS
               
    )

    SELECT  @Body = @TableHead + ISNULL(@Body, '') + @TableTail

    EXEC msdb..sp_send_dbmail
      @profile_name='PROFILE',
      @copy_recipients ='SQLDBA@XXX.COM',
      @recipients='AllITApplicationDevMicrosoft@XXX.COM',
      @subject='SQL DBA Supported Backup Status Alert for Production DB Servers',
      @body=@Body ,
      @body_format = 'HTML' ;

    -----------------------------------------------------------

    Code for DBA alert:

    ................................-------------------


    SET QUOTED_IDENTIFIER ON
    SET NOCOUNT ON

    USE MSDB
    GO

    IF (

    select
    COUNT(1)
    from [MSDB].[dbo].[CENTRALIZE_BACKUP_MAIN_ALL]
    where name not in (select databasename from [MSDB].[dbo].[CENTRALIZE_BACKUP_MAIN_ALL_DAILY] )
    ) >0
    BEGIN

    DECLARE @Body NVARCHAR(MAX),
        @TableHead VARCHAR(1000),
        @TableHead1 VARCHAR(1000),  
        @TableHead2 VARCHAR(1000),
        @TableTail VARCHAR(1000)

    SET @TableTail = '</table></body></html>' ;
    SET @TableHead = '<html><head>' + '<style>'
        + 'td {border: solid black;border-width: 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font: 15px arial} '
        + '</style>' + '</head>' + '<body>' + '<Centre><Bold>SQL DBA Supported Backup Failed Status Alert for Production DB Servers</Center>'+'<BR><BR><Bold>Report generated on : '
        + CONVERT(VARCHAR(50), GETDATE(), 106) +'::'+ CONVERT(VARCHAR(50), GETDATE(), 108)
        + '</Bold><br><br><br> <table cellpadding=0 cellspacing=0 border=0>'
     + '<tr>' 
        + '<td bgcolor=#E6E6FA><b>Server Name</b></td>'
        + '<td bgcolor=#E6E6FA><b>Database Name</b></td>'
        +'</tr>' ;

    SET @Body = (

    select
    TD = servername, '',
    TD = name,''
    from [MSDB].[dbo].[CENTRALIZE_BACKUP_MAIN_ALL]
    where name not in (select databasename from [MSDB].[dbo].[CENTRALIZE_BACKUP_MAIN_ALL_DAILY] )
    order by servername

                     FOR   XML RAW('tr'),
                      ELEMENTS
               
    )

    SELECT  @Body = @TableHead + ISNULL(@Body, '') + @TableTail

    EXEC msdb..sp_send_dbmail
      @profile_name='PROFILE',
      @recipients='SQLDBA@xxx.COM',
      @subject='SQL DBA Supported Backup Failed Status Alert for Production DB Servers',
      @body=@Body ,
      @body_format = 'HTML' ;
     
      END


    Santosh Singh


    Thursday, October 13, 2016 2:27 PM