Words: 691
Time to read: ~ 3.5 minutes
For the last while, members of the community and the dbatools team have been working in semi-secret in an effort to make your lives easier.
When asked if your SQL Server instances are up to scratch they want you to be able to answer, with the utmost degree of confidence, “Yes!”
As well as continuing their work on the dbatools module, they have created an open source PowerShell module designed for you to take, run, and show to whomever you like that your instances of SQL Server are valid.
The result of this is dbachecks.
Check yo’self!
You are checking your instances, right?
I’m reasonably confident that everyone has their database maintenance in place e.g. Statistics, CHECKDB, Backups, and Restores but what about other aspects?
- Are each of your database owners valid?
- Can you be sure that someone hasn’t created a database with a different collation from the server collation?
- Are your file groups balanced?
- Do you have memory dumps that you’re not even aware of?
A little hint: Yes, we can check those for you!
If you check out other posts by members of the team, such as Chrissy LeMaire ( blog | twitter), Rob Sewell ( blog | twitter), Claudio Silva ( blog | twitter ), or Jess Pomfret ( blog | twitter ) for example, you can get a glimpse of the capabilities of this module and the amount of effort poured into it.
I want to talk about something slightly different. I want to address those that will look at the module and say “What splendor! Alas it is not for us! Nay, ours is a beast of a different nature and we’ve had to give life to our own tests”
Inclusion for all
Straight away, dbachecks gives you the option to include or exclude checks that you feel aren’t for you. If you only want to run a subset of the checks, then you can specify that.
The real beauty that I think dbachecks provide is that you are getting a wealth of checks for things that you may never have thought of checking or known how to check while being able to add any personal tests as well.
Don’t belive me? Just watch
Let’s say in our example that we have a requirement that some other places do not.
We take daily backups and are required to have at least 30 days backups stored at one time.
Now we’ve already created a Pester test for this and, unfortunately, dbachecks does not have this. So what should we do?
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Describe –Name "Should have >= 30 backup files on storage" –Tag 'FilesOnStorage' { | |
Context –Name "Full backups for LocalTesting" { | |
It "should have greater than or equal to 30 backup files" { | |
Get-DbaBackupHistory –SqlInstance localhost\SQLDEV2K14 –Database LocalTesting –Type Full | | |
Measure-Object | | |
Select-Object –ExpandProperty Count | | |
Should –BeGreaterOrEqual 30 –Because "Business reasons say so" | |
} | |
} | |
} |
Personally I’d create an issue and then do a Pull Request.
I get it, I get it. You’ll do that when you have the time but you need this check included yesterday! So all because dbachecks doesn’t encompass this check, you feel it isn’t for you.
Yet if we import dbachecks Import-Module -Name dbachecks
and we run this little command Get-DbcConfig -Name app.checkrepos
(don’t worry about the name, Fred Weinmann ( blog \ twitter ) has already given you autocomplete) then we can see where dbachecks checks are stored.

Now here’s the magic!
If your tests are stored, say in “C:\Users\Shane\Desktop\In_Progress\Blogs\AddingChecksToDbachecks\“, then you can include them in dbachecks for your instance!
Running Set-DbcConfig -Name app.checkrepos -Value 'C:\Users\Shane\Desktop\In_Progress\Blogs\AddingChecksToDbachecks\' -Append
will add this directory to the configuration value!

Now when you run dbachecks, your tests can be included as well!
Let’s test it out, shall we?
Luckily, we gave our own check a unique tag “FilesOnStorage” so we can just call that and, say, the last full backup!
Invoke-DbcCheck -SqlInstance localhost\SQLDEV2K14 -Checks LastFullBackup, FilesOnStorage


AfterAll
There is so much for the DBA to do , to think about, and to learn about nowadays that it is very easy to get overwhelmed, very easy to overlook something.
dbachecks was created to help you and not to be “just another thing to learn”.
Go, for including their tests and your own. Go, for leveraging the expertise of others that you may not have yet. G,o for the piece of mind that it can give you!
Go give it a shot…
7 thoughts on “Adding Your Own Checks to dbachecks”