Monitoring Solution for SQL Database Backup Process

SQL Server database backup process is important to maintain the database availability. We can setup maintenance plan for the regular backup process. DBAs would require to monitor the backup process everyday whether all specified database backup  process is successful as well as needs to verify whether all newly created database has been included in the backup process or not.

It is better, if we have monitoring solution to check the backup process which checks database backup status automatically and reports to our mailbox in case of any issues in the backup process.

     I have developed a monitoring solution which checks the below mentioned criteria.     

• If [any of the database’s latest backup date is greater than 24 hours]
•  If [any of the databases is newly created but not yet been included in the backup process]

Script to monitor the backup process

USE master

SELECT

          @@SERVERNAME AS Server,

          ISNULL(CAST(SERVERPROPERTY('instancename')As Sysname),'DEFAULT') AS [Instance Name],

          msdb.dbo.backupset.database_name  COLLATE DATABASE_DEFAULT AS [Database],

          MAX(msdb.dbo.backupset.backup_finish_date) AS [Last Backup Date],

    DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [Backup Age (Hours)],

          'No Backups in last 24 hours' AS Description

FROM       

          msdb.dbo.backupset

          INNER JOIN dbo.sysdatabases

                    ON msdb.dbo.backupset.database_name COLLATE DATABASE_DEFAULT = dbo.sysdatabases.name COLLATE DATABASE_DEFAULT

GROUP BY msdb.dbo.backupset.database_name

HAVING (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(dd, - 1, GETDATE()))

UNION

--Databases without any backup history

SELECT    

          @@ServerName as Server,

          ISNULL(CAST(SERVERPROPERTY('instancename')As Sysname),'DEFAULT') AS [Instance Name],

          master.dbo.sysdatabases.name COLLATE DATABASE_DEFAULT AS [Database],

          NULL AS [Last Backup Date],

          9999 AS [Backup Age (Hours)],

          'No backups' as Description

FROM

          master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset

       ON master.dbo.sysdatabases.name COLLATE DATABASE_DEFAULT = msdb.dbo.backupset.database_name COLLATE DATABASE_DEFAULT

WHERE

                    msdb.dbo.backupset.database_name IS NULL

                    AND master.dbo.sysdatabases.name <> 'tempdb'

ORDER BY

                    msdb.dbo.backupset.database_name

                   

If we have multiple SQL Server instances, we create a SQL Table to store the name of the all SQL Server instances. Above mentioned SQL script can be used through OSQL Statement inside the batch script and loop through each SQL instances.