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