Database recovery is a critical task for database administrators. It involves executing the necessary steps to backup, save, retrieve, and restore databases from hardware, software, and user errors. To ensure the effectiveness of database recovery procedures, regular testing of backups is essential.

One common mistake in backup monitoring is only checking for backup success or failure. However, there is a third possible outcome – nothing. This occurs when a database or SQL instance is not configured for backups or when the SQL Agent job or Windows services are in a hung state. To achieve effective backup monitoring, it is important to consider the non-event of a lack of backup.

Regardless of how a database is backed up, whether through SQL Server native backups or commercial backup software, a row is written to the msdb.backupset table. This information can be used to create daily monitoring and backup reports.

Monitoring Daily Backups

To monitor daily backups, you can use the following query to find all databases that have not been backed up within the past day:

SELECT MAX(ISNULL(DATEDIFF(dd,ISNULL(b.backup_start_date, '01/01/1900'),GETDATE()),0)) AS 'NumDays' , d.name as 'DBName'
FROM master..sysdatabases d
LEFT JOIN msdb..backupset b ON d.name = b.database_name AND b.backup_start_date = (SELECT MAX(backup_start_date) FROM msdb..backupset b2 WHERE b.database_name = b2.database_name AND b2.type IN ('D','I'))
WHERE d.name != 'tempdb'
--AND d.name NOT IN (SELECT db_name FROM dbautility.dbo.db_exclude)
AND DATABASEPROPERTYEX(d.name, 'Status') = 'ONLINE'
GROUP BY d.name, b.type, b.backup_size
HAVING MAX(ISNULL(DATEDIFF(dd,ISNULL(b.backup_start_date, '01/01/1900'),GETDATE()),0)) > 1

You can automate daily backup monitoring using this query through SQL Agent or Microsoft Operations Manager/System Center Operations Manager. You can also add exclusions by creating a db_exclude table and uncommenting the exclusion line in the query.

Reporting Backup Information

In addition to monitoring daily backups, you can also generate backup reports across servers and databases over different periods of time. This is especially important for organizations where backup responsibilities are performed by a separate group from the DBAs.

To create a backup report, you need to define a collection process to centralize backup information and create SQL Server Reporting Services (SSRS) reports to present the data. The article provides setup scripts for creating the necessary database objects and tables.

Once the tables are created, you can populate the server_dim, exclude_db_dim, and dt_dim tables. The server_dim table should contain the servers you wish to collect backup information from. The db_exclude_dim table can be used to exclude specific servers or databases from analysis. The dt_dim table is populated using the fnSeqDates function to define the backup window for each date.

After populating the tables, you can create procedures to insert, update, and analyze backup data. These procedures are included in the provided scripts.

Collecting the Data

The data collection process involves running a PowerShell script, backupAnalysis.ps1, which collects backup information from the target server and inserts it into the centralized backupset_dim table. The script can be scheduled to run periodically using SQL Agent.

After collecting the data, you can run various queries to generate insightful reports. The article provides examples of queries to calculate overall backup success, backup success by month, week, and weekday, and backup success by server or database.

Generating Reports

To simplify reporting, a stored procedure, usp_backup_analysis_rpt, is included in the download. This procedure allows you to run any of the provided queries and specify different grouping levels.

The article also includes sample reports that can be configured using Visual Studio or SQL Server Business Intelligence Studio. These reports can be run locally or deployed to SQL Server Reporting Services Server.

Conclusion

Verifying that your databases are being backed up is a crucial task for database administrators. By following the process described in this article, you can effectively monitor and report on daily backups. This will help ensure the success of your backup procedures and meet any operating level agreements (OLAs) you have in place.