Best Practices Policy Based Management from PowerShell

I'm working on my Policies (SQL Server 2008 Policy Based Management) for my environments that I manage. I use them to check the maintenance, show system state, and in some cases, to prevent actions from occurring when I don't want them to. I was asked if Microsoft provides any "out of the box" Policies, and the answer is "yes".

You might remember the SQL Server Best Practices Analyzer, or BPA. While that tool is still around, many of the "rules" it used are now Policies that you can either import or just run from the file locations. If you took the defaults when you installed, they are here for 32-bit systems:

C:\Program Files\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033

And here on x64:

C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033

I normally run them in a PowerShell script, and then use the results to do something else. Here's the command on my system that I use to check that the data and backup files are on different drives, a common best practice:

# Run Policies from Command Line
# Can place this in an Agent Job
# 32-bit: 
# cd "C:\Program Files\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033"
# x64:
cd "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033"
Invoke-PolicyEvaluation -Policy "Backup And Data File Location.xml" -TargetServerName "bwoody1\SQL2K8"

As always, this warning applies to any script you find anywhere, including here.

Skip to main content