Thursday, 20 October 2011

Custom Backup Monitoring with Policy-Based Management and ExecuteSQL()

Configuring backups is possibly the most important task a DBA can perform. But a backup that ran perfectly yesterday and today may, for all sorts of reasons, misbehave tomorrow. To be confident that you have at least a fighting chance of recovering your database, you need a monitor your backups.
A simple approach is to have an agent job that executes a stored procedure daily and sends you the results through Database Mail. The stored procedure could list all databases that haven’t had a backup in, say, the last 24 hours. This is perfectly satisfactory if you only manage a small number of instances, but lacks scalability.

SQL 2008 introduces the Central Management Server, which allows a single action, such as executing a T-SQL command, to be performed against multiple servers. SQL 2008 also brings us Policy-Based Management, which allows the properties of many SQL Server facets (less technically known as things) to be tested, monitored and managed through a policy. The @LastBackupDate property of the Database facet bills itself as returning ‘The date and time when the database was last backed’. Feed this into a condition that expects @LastBackupDate >= DateAdd(‘hh’,-25,GetDate()), wrap it in a policy and you have the makings of a multi-server monitoring solution (see Ashish Kumar Mehta on for a blow-by-blow guide.) Throw in some automation and reporting using the Enterprise Policy Management Framework and you have a solution.

Or do you? I’ve written previously about how rows are written to msdb.dbo.backupset with is_snapshot = 1 whenever the VSS framework is invoked to make a database quiescent. In my case, this happens whenever whole-VM backups are performed by Netbackup 7.1 for VMWare.  I wanted my monitoring to disregard these backups, as I wouldn’t use them if I was performing a point-in-time recovery. However, the @LastBackupDate property of the Database facet is evaluated through a fairly straightforward query on the msdb.dbo.backupset table and makes no distinction between VSS and non-VSS backups. (And even if you are happy with the way @LastBackupDate works, eventually you’re sure to find another property that doesn’t behave how you want, or have a monitoring need that just isn’t covered by the supplied facets). If only we could write our own custom property or facet … but that option just isn’t available.

What we can do, however, is write a custom condition that can call our own piece of T-SQL, wrapped in an ExecuteSQL() function. ExecuteSQL() isn’t covered in the official documentation, but there are a number of blogs (some listed below) that describe how to use it.

To solve my particular problem, I proceeded like this:
Create a new policy and select New Condition


Name the condition and scope it to the Database facet. This means that the condition will be evaluated against each database in the instance.

Here is the magic. Create a new expression and in Field column enter:
Here we see the ExecuteSQL() function. The first argument specifies which datatype will be returned, the second argument is the SQL that will be executed. As the SQL will executed once for each database, DB_NAME() is used to capture the current database context. The ISNULL logic is there simply to deal with the possibility that the database has never been backed up.
Complete the expression by saying that the datetime value returned must be no more than a day old:clip_image001[10]
Finally, configure which databases you want the policy to be evaluated for and set the evaluation mode to ‘On-Demand’:

Once you have the policy configured, you can then run it against multiple instances registered with a Central Management Server:
Of course, this is still a manual evaluation. If you wish to automate the evaluation of policies against multiple targets, take a look at the Enterprise Policy Management Framework.

Policy-based management combined with Central Management Servers allow can be used to monitor the state of multiple servers. The ExecuteSQL() function allows custom conditions to be created when the pre-supplied monitoring facets are not suitable.

MSDN - Central Management Servers
MSDN - Policy-Based Management
Enterprise Policy Management Framework.


  1. Your query of the backupsets table is also much more efficient than the one used to find @LastBackupDate. On one of my servers, went from taking 2.5 seconds per db to 7 ms. That made a huge difference because the server has 150 dbs


  2. Thank you for the work done, I found your article very useful, as I'm just starting to learn sql, your information helped me
    Richard Brown data room solutions


Please leave a comment.