Skip to content


Pester dbatools for SQL best practices

Pester is a testing framework and it can be used to automate the testing of your PowerShell code. It provides a language that allows you to define test cases. There have been a few posts on Pester and dbatools including:

Andrew’s post recent post pushed me to go ahead and do my own. With what I have learned from Pester, it seems to be best to test each command on its own. That got me to thinking, what if I walk into a large environment and I want to see an enterprise summary of best practices? I started working on a few different versions with the ultimate goal of using dbatools function Get-DbaRegisteredServer. I have included a few examples of Test functions that include best practices, as well as some default database best practices all from dbatools.

In my first run, I wanted to make sure that I can pass multiple servers with multiple tests and came up with the below script. You will notice the top section that uses the Get-Dba functions, I had to supply my own values of the properties I am testing. In the bottom section, you will notice that dbatools Test-Dba functions have the IsBestPractice property already and that is all you have to test against.

Look at those results! You notice “Memory” is not set correctly on one system and “TempDb” is not set correctly on another. Since this worked, I had to make one code change so I can call it from another script for a summary level. I commented out the instances I was checking manually and then put in a parameter so I can pass multiple servers from another script.

Next, I decided I want to only see failures. With Pester, to show failures, all you have to do is use parameter -Show Fails after Invoke-Pester. I also wanted to receive an email on those failures across the enterprise in case a change was made and I was not near a system. Note: If you plan to utilize the script, please make sure you update the Send-MailMessage parameters.

The Email output looks like the following:

There we go! My first Pester script and tests across an enterprise environment. I will be re-visiting this post down the road once I learn more about Pester. I cannot wait for the latest beta build to be pushed to the Gallery. There are some new -Should commands that I would like to utilize.

Leave a Comment