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

Warning Handling in dbatools Automation Tasks

So I’ve been using dbatools for automated restore tasks and came across a SQL Server Agent job that I wrote that was reporting success but the job was actually failing.

What I found was the function I used, Restore-DbaDatabase, was not able to access the path that I was trying to restore databases from. The Restore-DbaDatabase function, and all dbatools functions according to the dbatools team on Slack, will throw a Warning rather than an Error by design.

When scheduling PowerShell scripts using dbatools in SQL Server’s Agent, we need use the SQL Agent Subsystem CmdExec so we can load in additional modules.  So we’ll have a SQL Agent job step that looks like this.

SQL Agent Job - cmdexec

 

Now, you see that line “Process exit code of a successful command” and it’s set to 0, we’ll that’s the first thing that I tested. I wanted to see if the warning generated by Restore-DbaDatabase returned a non-zero value…it didn’t it returns 0.  You can check this by checking %ERRORLEVEL% when running the PowerShell script defined in this job step’s command box at the command line.  

powershell.exe -file C:\test\Copy-TestDB1.ps1
echo %ERRORLEVEL%

These scripts are very small, most only do one thing…restore a database. So I want them to report failure when something goes wrong, so how can we get that warning to cause the SQL Agent job to report failure?

We have to options here

Our first option is to adjust how our session handles warnings, we can do that with 

$WarningPreference = “Stop"

Doing this will cause the script to stop executing when it hits the warning and then the job will report failure.  

Our next option is to use the -Silent parameter on our Restore-DbaDatabase function call. The -Silent parameter cause the warnings in our script to report as errors. 

Restore-DbaDatabase -SqlInstance $destination -Path $backupDirectory -Silent

Both of these options cause the return value of our CmdExec subsystem’s call to the powershell.exe to return 1…which will cause our Agent job to report failure. This is exactly what I want!

One other thing I tested, both of these options cause the script to stop at the point of the error. When using -Silent, the function returns what it tried to do to standard output. When using $WarningPreference I did not get that output.

Thanks to Friedrich Weinmann and Shawn Melton for helping me sort this all out!

T-SQL Tuesday

Thanks to SQL DBA with A Beard for this event – https://sqldbawithabeard.com/2017/09/05/tsql2sday-94-lets-get-all-posh/

The post Warning Handling in dbatools Automation Tasks appeared first on Centino Systems Blog.

Anthony Nocentino's Blog

Anthony Nocentino is the founder of Centino Systems. As an Enterprise Architect he works with clients to find right technology for their business, designing and deploying it, providing expertise on system performance and architecture. Creating well-designed, maintainable SQL Server and Linux based systems that enable clients to collect meaningful data that they can act upon. Anthony has a Bachelors and Masters in Computer Science and is working towards a Ph.D focusing on high performance/low latency data access algorithms on solid state disks. Anthony has a unique blend of academic and professional experience leveraged to help customers solve their hardest IT problems.

Comments

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

Loading comments...