Checking Job Step Output Mid-Job using PowerShell

Words: 627

Time to read: ~ 3 minutes

The XY Problem

Them: We have this job…

Me: Right…

Them: … and one of the steps in the job creates files…

Me: Okay…

Them: … and we need you to check if it creates the files, otherwise we don’t need to do any of the rest of the steps.

Me: Why don’t use just error out if that step fails?

Them: Cause there are other errors in that step but they don’t mean it failed

Me: … say what?

Pretty close representation of the conversation


I’m going to ignore the whole “there are other errors” for the moment and actually attempt this task. First, let’s try to see if there is a way to get the last time a job step ran.

I already have a test SQL Agent job called “TestEmail” so let’s use that for our test.

(Get-DbaAgentJob -SqlInstance localhost -Job 'TestEmail').JobSteps

Glancing at the output, it appears that we’re looking for the LastRunDate property. In this screenshot, it shows 1/1/0001 12:00:00 AM which means it’s never run before.

Creating Files

We need a way to create files, and not create files, on demand.

Vaguely in the back of my head (and I apologise for not remembering whom), I remembered someone using the presence of a temp table to fire or not fire a trigger. We’re going to use that premise here.

In a SSMS window, we have this code:


/* Create the files */
DROP TABLE IF EXISTS dbo.DoNotCreateFiles;

/* Do not create the files */
CREATE TABLE dbo.DoNotCreateFiles (DoNotCreateFilesID int NOT NULL);

If we want to create files from the PowerShell script, we need to drop the table.
If we don’t want to create files from the PowerShell script, we need to ensure the table exists.

Next, we create this PowerShell file which I’ve called “CreatePowerShellFiles.ps1“.

$Query = @'
IF EXISTS (SELECT 1/0 FROM [sys].[tables] WHERE [name] = N'DoNotCreateFiles')
    SELECT CreateFiles = 0;
    SELECT CreateFiles = 1;

[bool]$CreateFiles = (Invoke-DbaQuery -SqlInstance localhost -Database __DBA -Query $Query).CreateFiles

if ($CreateFiles) {
        Name = 'CreatedFile'
        DateCreated = Get-Date
        Nonce = New-Guid
    } | Export-Csv -NoTypeInformation -Path "C:\Users\shane.oneill\Desktop\TestPowerShellCreatedCode_$(Get-Date -Format FileDateTime).csv"

Adding this file as a step in our job, it checks for the existence of our table – if the table exists it does nothing otherwise it creates a sample csv file.

Now for the main course

We’re going to add another step now. This one will check for files created after the previous step has run.

First, we’ll create a PowerShell file (“CheckPowerShellFiles.ps1“).

param (


    [ValidateScript({ Test-Path -Path $PSItem })]

$Jobs = Get-DbaAgentJob -SqlInstance localhost -Job $JobName

$LastStep = $Jobs.JobSteps |
    Where-Object Name -eq $StepName

$FilesExist = Get-ChildItem -Path $FileDirectory |
    Where-Object LastWriteTime -ge $LastStep.LastRunDate

if (-not $FilesExist) {
    $ErrorMessage = 'Files were not created after {0}' -f $LastStep.LastRunDate
    throw $ErrorMessage

And add it to the job, passing in the parameters that we want:

Test Run

We’ve got two states that we want to test

  1. The files get created.
    1. Job should succeed.
  2. The files don’t get created.
    1. Job should fail.

Let’s run the first test:

  • Make sure the table is dropped so we create the files:

/* Create the files */
DROP TABLE IF EXISTS dbo.DoNotCreateFiles;
  • Now run the job:


Now to check that the job will fail if no files get created:

  • Make sure the table exists so no files get created:
/* Do not create the files */
CREATE TABLE dbo.DoNotCreateFiles (DoNotCreateFilesID int NOT NULL);
  • Now run the job:
Congrats, you have successfully failed

Taking a look at the job history, we can see our error message:


Well, we’ve proved that this method works!

I can pass on “CheckPowerShellFiles.ps1” to the people who requested the check telling them that they only need to add in the right values for the parameters…

Along with a polite note along the lines of “you should really fix your errors”.

Hiding Warnings in dbatools.

Words: 726

Time to read: ~ 4 minutes

Warning by default

dbatools warns by default.

From what we’ve seen, beginners to PowerShell do not want to be greeted by a sea of red when they use, mis-use, or abuse a dbatools command.

PowerShell errors normally contain useful information on what went wrong. With this information, the “what went wrong” can be fixed.

That being said, if you are writing PowerShell scripts and not raising valid error messages then I highly advise you to go back and fix that.

dbatools raises these error messages as friendly warning messages since we’ve found people will read a warning message quicker than they will read an error message.

Enabling Errors.

dbatools has a common parameter called -EnableException which changes this behaviour. When you use this switch parameter with a dbatools command, it should change the lovely, user-friendly warning to a lovely-but-unfairly-treated error message.

Here is the default process that raises a warning.

# Get the first backup from this path.
$BackupPath = (Get-ChildItem -Path D:\BACKUPS\ -Filter *.bak)[0]

# Restore it to a SQL Server instance that does not exist.
$BackupPath | Restore-DbaDatabase -SqlInstance DoesNotExist -OutputScriptOnly
Works on PowerShell 7.0

Here is the process when we use -EnableException that raises an error.

# Restore to a SQL Server instance that does not exist with errors.
$BackupPath | Restore-DbaDatabase -SqlInstance DoesNotExist -OutputScriptOnly -EnableException
Even shows the line.

Hiding the messages.

Depending on whether you use the default parameters or add in -EnableException parameter, you have ways to hide these messages.

Hiding default warnings

# Create a splat to save me typing over and over again.
$RestoreParam = @{
    SqlInstance      = 'DoesNotExist'
    OutputScriptOnly = $true

# Warnings actions
$WarningToDo = @{
    WarningAction   = 'SilentlyContinue'
    WarningVariable = 'RestoreWarning'

# Hide and capture the warning.
$BackupPath | Restore-DbaDatabase @RestoreParam @WarningToDo
Warning! Warning!

Here we can see that the warning message was not output but it was captured to our variable called “RestoreWarning”.

Hiding Errors

Errors are slightly different and I’ll show you why, although you can do the same thing, I use a try ... catch ... block instead.

I also don’t try and hide errors but relay them out.

try {
    $BackupPath | Restore-DbaDatabase @RestoreParam -EnableException
} catch {
    "Error happened! Here is the error message"
Try and catch

I was originally worried that the above method meant that you couldn’t reference the error that just occurred like we could with the warning methods and $RestoreWarning but I had forgotten about the $error variable!

# What was the latest error that we had?

I highly recommend that you capture that latest error message though $RestoreError = $Error[0] because the next error will be put on that stack and our error will be pushed down!

Hide the error

However, you can hide the error message that is raised by either an empty catch block or an -ErrorVariable.

Empty catch block

Please don’t do this.

I’m happy that PSScriptAnalyzer has a way to check for empty catch blocks so it is incentive to not do this. However it is possible.

# Empty catch = Boo!
try {
    $BackupPath | Restore-DbaDatabase @RestoreParam -EnableException
} catch {


Do I like it? No. It is possible? Yes.


I mentioned that capturing errors with -ErrorVariable is a bit different and I’m going to show you why.

First let’s show that using -ErrorVariable can work to capture the error stream and stop it showing on the console.

$BackupPath | Restore-DbaDatabase @RestoreParam @ErrorsToDo -EnableException

And now here is the difference between -WarningVariable and -ErrorVariable.

# Count the number of objects in these variables.
order by int or string, it works…

Let’s take a look at $RestoreError so.

2 different types of objects
All the info

If you are wondering why this is I believe it’s because when we use -WarningVariable method we are letting dbatools do the commands, bubble up the main error into a single warning, and then we are capturing that warning message.

When we use -ErrorVariable method, we are saying “give me everything”, there is no bundling happening, and we are seeing all the errors that occur – including internal functions and cmdlets that dbatools use.

All in All

I like the way that dbatools shows errors as warnings by default for everyday and beginning users.

I appreciate the fact that dbatools allows us to turn of that behaviour for our scripts and tool making purposes.

However, if you want nothing, you can have that too.

Finally, like everything, know your tools so you can get the most use out of them!

dbatools 1.0. The tools to break down barriers.

Words: 780

Time to read: ~ 4 minutes

Version 1.0 is coming…

20th June 2019 and dbatools will release version 1.0

Now, I don’t remember how I started with dbatools.
I know that I was a Junior DBA at that stage and I was experimenting with PowerShell.

After being initially introduced to PowerShell when it was only Version 2, and dismissing it for something that wouldn’t catch on in a Windows environment (I never said that I was prophetic!), I was ready to attone for my actions and dive deep into this wonderful scripting language.

They say that the way to improve is to do and I was looking for a project that interweaved PowerShell with SQL Server.
I can only assume that was when dbatools found me.

Now some time has moved on and things have moved on as well.
PowerShell has released verison 7 on pre-release, I’ve taken a position as a Production DBA, and dbatools are about to release version 1.0!

In that time, I’ve learned that dbatools, more than just a PowerShell module, helps break down barriers that exist in organisations.

Barrier to learning…

This may come across as a bit banal, something that has been thrown around a lot, and something that you have all heard been said before but there are so many new things out there for data professionals.

How are we supposed to keep up-to-date with developments, how are we supposed to keep abreast of the latest technologies, how are we supposed to drink from the water hose of learning if we are not taking advantage of the automation that dbatools brings?

I cannot imagine a data professional taking advantage of containers, spinning up a few, and then saying

Hold on, I have to connect SSMS to the container to restore a database on it now. Oh wait, I have to copy and paste the backup file over first. Then I have to make sure permissions are there and correct and…yeah you need to wait.

Not me

Imagine it! Imagine the speed and progress taking place.

  • Find an image: docker search and its done!
  • Download it: docker pull and its done!
  • Create the container: docker create and its done!
  • Deploy the database: “ah hold on guys, this is going to take at least an hour...”

The flow is cut.
Its speed-bumped; road blocked by a data professionals lack of automation.

dbatools can help you, it has the commands. Restore-DbaDatabase, Copy-DbaLogin, and the work is done!

It is not that there is no time to learn the new technologies, it is not using automation to give yourself time to learn.

Use dbatools, push the speed-bump down the pipeline, and leverage that new found time to learn to become more valuable!

Barriers in roles…

DevOps is the union of people, process, and products to enable continuous delivery of value to our end users

Donovan Brown

Now imagine trying to implement DevOps in a organisation where the people are siloed?

There is no collaboration, no communication because people don’t have the knowledge or the tools to provide support to anyone else.

Developers have to wait for the DBA each time they want to restore a database on a dev environment.

Operations can’t troubleshoot an issue on the database because they don’t have the permissions or the know-how to connect to an instance.

Middle management have to go to every single instance to run the same query for information about their SQL Server estates.
RDP, SSMS, New Query, Paste, Run over and over and over again.

dbatools empowers these people.

Tools can be written using dbatools that restores a masked database for developers. This can be logged and as open or as restrictive as you want.

Operations can use Install-DbaWhoIsActive and Invoke-DbaQuery to check out issues on an instance without having the bother the DBA first.

Middle management can be given a script to query all the servers from Get-DbaRegServer and Invoke-DbaQuery to run what they want. What was once a multitude of mouse clicks can be reduced to the running of a single file.

Everything logged, everything known, and everyone empowered.

dbatools breaks down these barriers…

dbatools is about to publish version 1.0 but this does not mean that the work stops.

There are knowledge seekers to help, Issues to troubleshoot, Feature Requests to pour over, Documentation to create and update, Tests to write and verify.

Do not be put off; version 1.0 is something to celebrate, not something to be intimidated by.

Join in!
Learn, laugh, and lend a hand.
Version 1.0 is released but, thankfully, numbers don’t stop at 1.

Now this is not the end. It is not even the beginning of the end. But it is, perhaps, the end of the beginning.

Winston Churchill

Finding Parameters that do not match Column Names

You think this will take me hours? Ha! Think again.

Words: 437

Time to read: ~ 2 minutes

Script Link:

Continue reading “Finding Parameters that do not match Column Names”