Dbatools

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!

I had a great time speaking at the Kansas City SQL Server User Group on Thursday, February 18th. The more I present my Backup Basics with PowerShell and dbatools session, the more I enjoy it. And each time, I’ve learned something new myself.

In one of the demos I show how Restore-DbaDatabase selects and restores the most recent backup of a database when pointed at a directory holding several backups of a single database. Dan Maenle (twitter) asked how the function knows which one is the most recent. Timestamps? Filenames? Really good guesses? My takeaway from the session was to confirm the method in use.

While presenting Backup Basics with PowerShell and dbatools at ONDT, I mentioned a caveat with the -ReplaceExisting switch for Install-DbaMaintenanceSolution. This switch drops all of the objects installed by the Maintenance Solution, including the CommandLog table. If you use that table to produce evidence that the various maintenance tasks are being performed on a regular basis or use it to track performance of those tasks over time, dropping that table presents a problem.

I had the honor of presenting a new session, Backup Basics with PowerShell and dbatools, at the first Ohio North Database Training meeting this evening. Thank you to the group leaders for the opportunity and to everyone who attended.

Because this was both a brand new session and an attempt at a new way of delivering the presentation, I had a couple tech glitches but things went pretty well once they were understood and ironed out. Definitely room for improvement next time!

Chrissy LeMaire (blog | twitter) pinged me earlier this week to tell me about Github Discussions. It’s a new feature of Github which is similar to Stack Exchange, but much more focused - it’s just for your project/repository! Get help, start discussions, share tips & tricks.

She’s enabled this feature for the dbatools repository and it’s open for business. Go check it out and start posting questions, answers, or both!