Policy-Based Management(Check The Databases That Are Not Backed Up-Database Facet)

Policy-Based Management is a feature that introduced with SQL Server 2008. It allows us to set the rules we want in our systems, to determine our standards and to perform our controls automatically. For example, we can create a policy for control purposes so that the names of our stored procedures do not start with a number or database recovery models are not simple.

To comprehend “Policy Based Management (PBM)”, you need to understand some concepts.

Facet: A feature that can be managed by the PBM. For example, there is a facet named database. And by using this facet, you can create a policy that controls the databases that are not backed up.

You can access all facets via SSMS as follows. You have to double-click on Facet details.

Condition: checks whether the sub properties of the related facets meet the specified condition.

Let’s create a policy that checks databases that are not backed up.

We click New Policy on the Management> Policy Management> Policies tab as follows.

We give a name to Policy and we need to create a condition from Check Condition.

We click on New Condition.

We give a name to the condition from the Name section.

We choose the Database facet in the Facet section.

By clicking “…” in the Expression section, we select @LastBackupDate, which is the sub property of the Database facet.

We select “>=” in Operator and write “DateAdd(‘HH’, -24, GetDate())” in Value.

Thus, if there is a database that has not been backed up in the last 24 hours, the policy will fail.

In the DateAdd function, we include HH in single quotes. Normally the DateAdd function is a function that works without a single quotation. If you run this function via SSMS without removing the single quotes in HH, you will get an error. If you add it to the Value section without adding a single quotation, you will receive the error as follows.

Policy expressions require the first argument of DATEADD and DATEPART functions to be in single quotes.

By selecting “On Schedule” from the Evaluation Mode section, we determine at what intervals the policy will be checked, and then click the Enable check box.

If we choose On Demand, we only do checks when we execute policy.

In the server restriction section, if you have a server-based condition, you can control your condition by creating a server-based condition.

If we want to automatically check at regular intervals, we click new in schedule and determine the frequency of the policy check. As you can see below, we set it to work once every day at 00:00:000.

We created our policy. To run manual, we click Evaluate as follows.

When I performed this operation in my local, I got a result like this. Because I’m not backing up my local databases.

When you refresh on Instance, a red color x mark appears next to Instance that looks like the letter. The reason for this is that there is an unsuccessful policy that SQL Server wants to notify us. When you see this mark, you must look at the defined policies and correct the problems.

There are many controls you can do on SQL Server with Policy Based Management. And I think a professional database administrator should use this feature that SQL Server provide us with all the details. You can see other controls you can do with Policy Based Management by typing “Policy Based Management” in our search section.