SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Running dbatools commands with VS Code tasks

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: –

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!


The DBA Who Came In From The Cold

I’m Andrew Pruski and I am a SQL Server DBA with 5 years experience in database development and administration.

The online SQL Server community has helped me out immensely throughout my career, whether from providing reference material in blog posts, or answering my (sometimes obscure) questions on forums. So, to try and say thank you, I would like to contribute my own experiences in the hope that they could benefit someone out there.

So here’s my general ramblings and thoughts about working as a SQL Server DBA.

You can find me on twitter @DBAFromTheCold

If you have any feedback on my blog please send me an email to dbafromthecold@gmail.com.

Comments

Leave a comment on the original post [dbafromthecold.com, opens in a new window]

Loading comments...