Powershell

A little while back, I offered up a one-liner to scan your SQL Server instances and report which ones are out of date. But what if you need to take the next step, determining which updates need to be downloaded? That’s exactly what Josh asked on the SQL Community Slack recently.

Can Test-DbaBuild also bring back the KB number? From reading the docs it looks possible as it does return the compliant version form the .json file, or at least I think it does 😀. The intention is if the KB is returned, then one could pipe it through to Get-DbaKbUpdate and/or Save-DbaKbUpdate to have the KB’s automatically downloaded to the desired path.

It turns out I was doing this all wrong for months.

For the longest time, I’ve been checking my SQL Server instances to see what needs patching with Test-DbaBuild from the dbatools PowerShell module. But the result was always the same - it never returned a Service Pack or Cumulative Update target. I glossed over it because I knew what the right answer was already, but recently I decided that wasn’t good enough. We need a reliable report to give to other people.

dbatools has a lot of functions. A lot. Over 550. There is a great command index on the website, and the documentation gets updated every time a new version is published. But sometimes, it feels like you can’t find what you need unless you already know the name of the thing you’re looking for. Other times, you might start writing your own wrapper around dbatools functions, or maybe start a new function from scratch, because it seems like the functionality you need isn’t there.

Or: Andy Authors An Amazing Alliteration

Do enough work with PowerShell and you’ll build up a decent collection of modules installed from the gallery into either your computer or your user profile (or maybe both!). Here are two one-liners to help keep things up to date and tidy.

Note: I’m calling these one-liners but I’ve inserted line breaks for readability here. These do count as one-liners as each one is a single, unbroken PowerShell pipeline.

Thanks to everyone who came out to see dbatools for the Uninitiated at SQL Saturday Albany on July 20th, 2019. I had a lot of fun sharing dbatools with you and hope you’re ready to start exploring on your own!

The slides and demo scripts are available in my GitHub repository.

If you have any questions about the session, please feel free to contact me via:

• Email
• Slack (@alevyinroc)
• Twitter

I am pleased to announce that I will be presenting at SQL Saturday #855 in Albany, NY on July 20, 2019. Join me at 2 PM in room LC05 for “dbatools for the Uninitiated”

You’ve just inherited a large SQL Server estate, and next month’s merger will double the number of instances you’re responsible for. Or maybe you have one big instance with thousands of databases on it. Are there backups? Are they good? Are your systems in good health?

Announced at DataGrillen 2019 today, the amazing dbatools PowerShell module has officially released version 1.0. This is a tremendous milestone for the best Open Source project built for data professionals.

What started out as a single PowerShell script for migrating SQL Server instances in Chrissy LeMaire’s (blog | twitter) datacenter has become the most important and comprehensive Open Source toolkit for SQL Server database administrators and developers. Whether you’re managing one server or one thousand, this module is an indispensable tool which will make your day more productive and less error-prone.

I was recently working on a PowerShell script to set up some new databases for my users and found myself writing the same things over and over again.

Invoke-DbaQuery -SqlInstance MyServer -Database $SrcDB -Query "select field1 from table...";
Invoke-DbaQuery -SqlInstance MyServer -Database $DstDB -Query "update table...";
Invoke-DbaQuery -SqlInstance MyServer -Database $DstDB -Query "insert into table...";

By the 4th Invoke-DbaQuery, I found myself thinking “this repetitive typing kind of sucks.” Then I remembered Chrissy LeMaire’s segment in the first PSPowerHour where she talked about default values, and her accompanying dbatools blog post. Most of the blog posts and demos of this feature focus on using it from the command line, so I had overlooked the fact that I could use it from within a script as well, and even change the values when looping.

In my previous post, I outlined the preparations we undertook to migrate a large SQL Server 2008R2 instance to SQL Server 2016. This post details migration day.

Final Prep

We completed our nightly backups as usual on Friday night, so when I arrived Saturday I kicked off a final differential backup to catch any overnight changes. We’ve multi-threaded Ola’s backup script by creating multiple jobs and I started them all at once with (of course) PowerShell.

A bit over a year ago, I blogged about my experience migrating a test SQL Server instance from a VM to a physical machine with a little help from my friends. That migration went well and the instance has been running trouble-free ever since. But it’s small potatoes. A modest instance, it’s only about 5% the size of production. With SQL Server 2008R2’s EOL looming, it was time to migrate production to SQL Server 2016.