Powershell

I don’t recall where this came up (probably in SQLSlack), but I had a need to install an older version of dbatools to test something related to loading the dbatools.library library/dependency.

I’m hosting T-SQL Tuesday this month and our topic is Managing SQL Agent Jobs. In this post, we’ll be taking a look at a very handy combination of dbatools functions for looking at agent job schedules & performance.

This is one of those blog posts you write so that 2 years later, you can look it up to remind yourself how to do something.

I found myself needing to figure out if “today” was the end of the month in PowerShell. In T-SQL, this is easy, as we have the EOMONTH() function. But PowerShell (the .NET System.DateTime struct) doesn’t have the same thing.

The week of PASS Data Community Summit 2024 (November 4-8), Bluesky seemed to reach critical mass with the data community as well as the tech community writ large. Over the week, I saw a few posts (“skeets” I guess?) from Jeff Hicks saying that he was working on a PowerShell module for Bluesky. I said to myself “interesting, I’ll keep an eye on that.”

On the evening of November 11th, Jeff posted that he needed help troubleshooting an apparent issue on macOS.

Sometimes, work tasks and questions that come up in the SQL Server Community Slack coincide.

A couple weeks ago, DrData asked

With SET STATISTICS IO ON, is there really no way to show the TOTALS at the bottom? There are some nice tools like StatisticsParser but it sure would be nice to see the totals of all values right at the bottom, especially when there are many tables.

The task at hand in the office was a need to do the same thing, but with SET STATISTICS TIME ON. So I got to thinking…I can PowerShell my way through this, right?

The Problem

While performing an instance migration this spring, I happened upon something I didn’t expect in dbatools. It should have been a simple backup/restore copy of the databases, with the backup files residing on a fileshare on the destination server after being copied there. I kept getting a warning that the backup files I was attempting to restore couldn’t be read, and the restores (via Restore-DbaDatabase) wouldn’t execute.

I checked permissions on the server over and over again. Both on the filesystem and for the share that I was attempting to read from. Even more curious, if I executed the restore database statements directly from within Management Studio, the databases restored without issue.

The Prompt

In July, Brent Ozar (blog | twitter) asked us to make September Community Tools Awareness Month.

In September, I want you to improve community knowledge about one free tool that you rely on every week in order to get your job done.

I’m sure it will come as no surprise to readers of this blog, but I’m going to select the amazing PowerShell module dbatools.

dbatools Is for Everyone

Despite what the name may imply, dbatools is a valuable toolkit for anyone who needs to interact with SQL Server. It’s not just about upgrades and migrations anymore. I like to tell people it’ll make them look like a wizard to their colleagues because of how quickly things can get done with just a few commands.

A collection of the resources mentioned in my PASS Data Community Summit session Backup Basics with PowerShell and dbatools, including bonus content!

Over the summer, I spent some (a lot of) time working on updates to a script at work which runs multiple processes in parallel. Everything seemed to work OK for a while, but then everything broke. It broke right around the time dbatools 1.1 dropped, so I started thinking that something must have changed there. As it turns out, it was entirely my fault and I hope this post will help you avoid the same trap.

I am thrilled to announce that I will be speaking at this year’s PASS Data Community Summit! This year’s event runs November 8th through 12th. I am thrilled to announce that I will be speaking at this year’s PASS Data Community Summit!