I’ve started to look at the excellent dbatools.io to automate some of the checks that I routinely perform on my SQL instances.

But before I go into this post, I want to say a thank you to Cláudio Silva (t). The powershell commands that are below are based off the code he posted in his excellent blog Have you backed up your SQL logins today?

The first dbatools commands that I looked at are: –

• Get-LastGoodCheckDb
• Get-LastBackup

These commands do exactly what they say on the tin. Pretty standard stuff for DBAs but what’s cool is how we can use Visual Studio Code to quickly and easily check that all our databases are being backed up and have a recent (good) CHECK DB.

I’m going to setup two scripts to run the dbatools commands against my SQL instances via Visual Studio Code Tasks.

I don’t have a central management server so I created a database in my local instance of SQL to hold all my server names: –

CREATE DATABASE [DBA];
GO
USE [DBA];
GO
CREATE TABLE [monitoring].[CorporateSQLServers](
[ServerID] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [sysname] NOT NULL,
PRIMARY KEY CLUSTERED ([ServerID] ASC) ON [PRIMARY]) 
ON [PRIMARY]
GO

Once the table was created, I added in all the server names that I wanted to perform my checks against.

Next thing was to write the scripts. I took Cláudio’s script and modified (actually simplified would be a better description) it, firstly to get the last backup details of my databases: –

#Requires -module dbatools
#Get a list of instances where you will run the command through
$SQLServers = Invoke-Sqlcmd -ServerInstance "localhost" -Query "SELECT ServerName FROM [DBA].[monitoring].[CorporateSQLServers]"
#For each instance 
$SQLServers | ForEach-Object {
     Get-DbaLastBackup -SqlInstance $_.ServerName
} | Out-GridView -Wait

Then to get the last know good Check DB of all my databases: –

#Requires -module dbatools
#Get a list of instances where you will run the command through
$SQLServers = Invoke-Sqlcmd -ServerInstance "localhost" -Query "SELECT ServerName FROM [DBA].[monitoring].[CorporateSQLServers]"
#For each instance 
$SQLServers | ForEach-Object {
     Get-DbaLastGoodCheckDb -SqlInstance $_.ServerName
} | Out-GridView -Wait

N.B.- The -Wait option is there to prevent the grid output from closing when run as a task

I saved both of these scripts in a directory that Visual Studio Code was pointed at and opened the program.

Then I opened the command palette by running Ctrl + Shift + P and typed Task.

The first option is Configure Task Runner, select that and it will open up the tasks.json file within Visual Studio Code. This is where I can create tasks to run my powershell scripts.

I edited the file with the following code: –

{
    // See https://go.microsoft.com/fwlink/?LinkId=733558
    // for the documentation about the tasks.json format
    "version": "0.1.0",
    "command": "powershell",
    "isShellCommand": true,
    "tasks" : [
        {
            "taskName": "Corporate Database Backup Checks",
            "args": ["-ExecutionPolicy",
                    "Unrestricted",
                    "-NoProfile",
                    "-File","${cwd}/CorporateSQLChecks/LastFullBackup.ps1"],
            "showOutput": "always",
            "suppressTaskName": true
        },
        {
            "taskName": "Corporate Database Integrity Checks",
            "args": ["-ExecutionPolicy",
                    "Unrestricted",
                    "-NoProfile",
                    "-File","${cwd}/CorporateSQLChecks/LastGoodCheckDB.ps1"],
            "showOutput": "always",
            "suppressTaskName": true
        }
    ]
}

By doing this, I will then get the option to run both my scripts from the Visual Studio Code command palette.

Excellent stuff! I now have a quick and easy way to check that all my databases are being backed up and have a recent Check DB.

Thanks for reading!