What is dbachecks?

Open source powershell module maintained by SQL community contributing to it. dbachecks is a framework created by and for SQL Server pros who need to validate their environments. Basically, we all share similar checklists and mostly just the server names and RPO/RTO/etc change.

This open source module allows us to crowdsource our checklists using Pester tests. Such checks include:

  • Backups are being performed
  • Identity columns are not about to max out
  • Servers have access to backup paths
  • Database integrity checks are being performed and corruption does not exist
  • Disk space is not about to run out
  • All enabled jobs have succeeded


Client requirements

  • PowerShell 4+ is required.
  • Automatic installation of the dependent modules will only be provided via the PowerShell Gallery.

When you install from the Gallery, it’ll auto-install:

  • dbatools
  • Pester
  • PSFramework

When you import, it’ll auto-import

  • dbatools
  • Pester
  • PSFramework

If you have already installed the module and you update it, you may be required to update the Pester or the PSFramework modules before it will import. If you see a message like

Then you need to

Install-Module Pester -SkipPublisherCheck -Force
Import-Module Pester -Force

SQL requirements

dbachecks uses dbatools for most of it’s data gathering so it supports SQL Versions from SQL 2000 to SQL vNext including SQL running on Linux. (dbachecks will not install on PowerShell Core yet so can not be run on a Linux client) Obviously some of the Services adn disk space checks will not work against instances running on Linux as they are usin gWindows API calls.

Getting started

Checks are performed using Invoke-DbcCheck which is basically a wrapper for Invoke-Pester. This means that supported Invoke-Pester parameters work against Invoke-DbcCheck.

In this module, a “Check” is synonymous with a “Tag” in Pester. So you can Invoke-DbcCheck and specify a Check that you want to run. You can see a list of the available Checks with Get-DbcCheck.

Once you’ve decided on the Check(s) you want to run, it’s time to ensure you have a list of servers to run the checks against.

Making server lists

Similar to the dbatools module, dbachecks accepts -SqlInstance and -ComputerName parameters.

Invoke-DbcCheck -SqlInstance $servers -Checks SuspectPage, LastBackup

If you have a simplified (single) environment, however, you can set a permanent list of servers. “Servers” include both SQL Server instances and Windows servers. Checks that access Windows Server (e.g. disk space checks) will utilize -ComputerName parameter. A pure SQL Server command(s) (such as the backup check) utilizes the -SqlInstance parameter.

# Set the servers you'll be working with
Set-DbcConfig -Name app.sqlinstance -Value sql2016, sql2017, sql2008, sql2008\express
Set-DbcConfig -Name app.computername -Value sql2016, sql2017, sql2008

# Look at the current configs

# Invoke a few tests
Invoke-DbcCheck -Checks SuspectPage, LastBackup

What it looks like

Other ways to execute checks against specific servers

Additional Invoke-DbcCheck examples:

Invoke-DbcCheck -Check Backup -SqlInstance sql2016
Invoke-DbcCheck -Check RecoveryModel -SqlInstance sql2017, sqlcluster

$sqlinstance = Get-DbaRegisteredServer -SqlInstance sql2017 -Group Express
Invoke-DbcCheck -Check Backup -SqlInstance $sqlinstance

Invoke-DbcCheck -Check Storage -ComputerName server1, server2

Check and ExcludeCheck

We tag each of our Checks using singular descriptions such as Backup, Database or Storage. You can see all the Pester related Tags using Get-DbcTagCollection or Get-DbcCheck.

Each Check generally has a few Tags but at least one Tag is unique. This allows us to essentially name a Check and using these Tags, you can either include (-Check) or Exclude (-ExcludeCheck) in your results. The Exclude will always take precedence.

For example, the Database Tag runs a number of Checks including Backup Checks. The command below will run all Database Checks except for the Backup Checks.

Invoke-DbcCheck -Check Database -ExcludeCheck Backup -SqlInstance sql2016 -SqlCredential (Get-Credential sqladmin)

Reporting on the data

Since this is just PowerShell and Pester, results can be exported then easily converted to pretty reports. We’ve provided two options: Power BI and SMTP mail.

# Run checks and export its JSON
Invoke-DbcCheck -SqlInstance sql2017 -Checks SuspectPage, LastBackup -Show Summary -PassThru | Update-DbcPowerBiDataSource

# Launch Power BI then hit refresh

Sending mail

Includes a command to make emailing the results easier!

Invoke-DbcCheck -SqlInstance sql2017 -Checks SuspectPage, LastBackup -OutputFormat NUnitXml -PassThru |
Send-DbcMailMessage -To clemaire@dbatools.io -From nobody@dbachecks.io -SmtpServer smtp.ad.local

I’d like to run my checks in SQL Server Agent

Great idea! Remember that this module requires PowerShell version 4.0, which doesn’t always mesh with SQL Server’s PowerShell Job Step. To run dbachecks, we recommend you use CmdExec. You can read more at dbatools.io/agent.

If you do choose to use the PowerShell step, don’t forget to Set-Location somewhere outside of SQLSERVER:, otherwise, you’ll get errors similar to this

I don’t have access to the PowerShell Gallery, how can I download this?

This module has a number of dependencies which makes creating a GitHub-centric installer a bit of a pain. We suggest you use a machine with PowerShellGet installed and Save all the modules you need:

Save-Module -Name dbachecks, dbatools, PSFramework, Pester -Path C:\temp

Then move them to somewhere in your $env:PSModulePath, perhaps Documents\WindowsPowerShell\Modules or C:\Program Files\WindowsPowerShell\Modules.

Run same command on all SQL server databases

There are times when i find myself needing to run a SQL command against each database on one of my SQL server instances. There is a handy undocumented stored procedure that allows you to do this with a single line statement and does not need using cursors either.

Syntax for this undocumented procedure is:

EXEC sp_MSforeachdb @command

Example: Query information from all databases on a SQL instance

--This query will return a listing of all tables in all databases on a SQL instance: 
DECLARE @command varchar(1000) 
SELECT @command = 'USE ? SELECT name FROM sysobjects WHERE xtype = ''U'' ORDER BY name' 
EXEC sp_MSforeachdb @command 

You can exclude the @command variable. The command below also behaves the same as above

--This query will return a listing of all tables in all databases on a SQL instance: 
EXEC sp_MSforeachdb 'USE ? SELECT name FROM sysobjects WHERE xtype = ''U'' ORDER BY name' 

When I inherit a new SQL server to manage, one of the things i look for is the recovery model of the databases. This has caused me calls from sys admin teams stating that the disk is full. Few times the culprit is that the recovery model is set to the default as FULL. So you are logging every single transaction.

Unless you are running regular transaction log backups, it is recommended to switch the recovery model to SIMPLE. This option will discard the log as soon as a transaction is committed and written to the disk.

The following command will set the database recovery model on all user databases to SIMPLE.

exec sp_MSforeachdb 'IF ''?'' NOT IN (''master'',''tempdb'',''model'',''msdb'') BEGIN ALTER AUTHORIZATION on database::? SET RECOVERY SIMPLE END'

You are welcome to take the above one line command and modify the filtering above to add or remove the databases per your needs.

%d bloggers like this: