Saturday, 6 October 2012

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
          @@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
          INNER JOIN dbo.sysdatabases
                    ON msdb.dbo.backupset.database_name COLLATE DATABASE_DEFAULT = COLLATE DATABASE_DEFAULT
GROUP BY msdb.dbo.backupset.database_name
HAVING (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(dd, - 1, GETDATE()))
--Databases without any backup history
          @@ServerName as Server,
          ISNULL(CAST(SERVERPROPERTY('instancename')As Sysname),'DEFAULT') AS [Instance Name],
          NULL AS [Last Backup Date],
          9999 AS [Backup Age (Hours)],
          'No backups' as Description
          master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset
       ON COLLATE DATABASE_DEFAULT = msdb.dbo.backupset.database_name COLLATE DATABASE_DEFAULT
                    msdb.dbo.backupset.database_name IS NULL
                    AND <> 'tempdb'

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.