SQL Server 2008 Improvements Practicals – Policy based Management – Check Database Backups

I gave a presentation at TechEd 2008 where I covered our new features in "A Day in the Life of a DBA". Here's more of what I showed for 10:00am - "Panic Button":

Now that I've run my morning checks, I found a couple of databases that were in Simple recovery mode and that weren't backed up in the last 24 hours. You'll recall that I used the new Object Explorer Details selectable columns to find and sort on these values. But now I'm nervous, and what I need to do is check all databases, either for the condition of "Simple" Recovery model or those databases not backed up in 24 hours, or perhaps a combination of both of those. Not only do I want all of the databases on a single system, but all of my systems.

With the new Policy Based Management (PBM) enhancement, I can create a Policy to perform this check for me. In my demo, I create a simple policy that had a condition of "BackupDate > 1", and a Filter condition of "RecoveryModel=Simple". With these two conditions in place I can run the Policy across all of the systems in the organization, and get a report that shows the violations. I could also leverage these conditions for other Policies.

And here's another cool thing - I can use these Policies in  PowerShell. So I could:

  1. Create a Policy with the conditions I want

  2. Read a text file of server names

  3. Run the policy across those servers (even 2K5 and 2K systems in some cases)

  4. Save the results in a variable

  5. Do something based on that variable (like automatically back up the databases, or send an e-mail, or log the event....)


Skip to main content