Press "Enter" to skip to content

Category: Powershell

Deleting Old Build Definitions in Azure DevOps

Mark Broadbent solves a problem for us:

I have been experiencing a problem for quite a while now in my current environment, in that some of our old builds cannot be deleted. When you attempt to do so it results in the following error:

One or more builds associated with the requested pipeline(s) are retained by a release. The pipeline(s) and builds will not be deleted.

Many of our pipelines have undergone a lot of change over time to the degree it is not even obvious anymore why (or indeed where) these builds are being prevented from being dropped. The only thing that is clear is that until they can be, the old build definitions will remain.

Regardless of the reason why, Mark has the answer for how.

Comments closed

Powershell 7 and SMO

Max Trinidad gives us a primer on SMO with Powershell 7:

There are two ways you could use SMO in PowerShell 7 (cross-platform):

1. Installing the SMO NuGet packages, two packages are requiered:
a. Microsoft.SqlServer.SqlManagementObjects Version 150.18208.0 (as of 03/23/2020)
b. Microsoft.Data.SqlClient Version 1.1.1 (recommended)

2. Installing the PowerShell Module: SqlServer Version 21.1.18221 (as of 03/23/2020)

Keep in mind, once the packages and/or modules are installed, you need to update them manually.

Read the whole thing.

Comments closed

Creating Powershell Functions which Accept Pipeline Input

Patrick Gruenauer shows how to make your Powershell functions pipeline-capable:

You probably have already created your first function in PowerShell and now you want that your function is capable of pipeline input. Right? Ok, you’ve come to the right place. In this blog post I will carry out creating a sample function that accepts pipeline input by value.

Patrick also shows us one of the earlier hang-ups of pipeline development if you don’t have the correct process blocks.

Comments closed

Tips and Traps with PowerShell 7

Jeffrey Hicks takes us through some of the tricky parts of migrating to Powershell 7:

A long established community best practice in PowerShell scripting is not using command and parameter aliases. In a cross-platform world, this is even more critical. You may have been in the habit of using Sort in your code in place of Sort-Object. I know I have. I didn’t mind bending the no alias rule abit because there was nothing cryptic about Sort.

But in the Linux world, sort is a native command. There is no PowerShell alias. If your code uses sort, on Linux it will call the native command which will most likely break your code.

Read on for several more hints.

Comments closed

Preparing for Demos with Pester

Jess Pomfret has some advice for you if you ever give a presentation:

If you don’t know what Pester is, it’s a test framework for PowerShell.  In the simplest explanation, using their Domain-Specific Language (DSL) you describe how things should look. If all looks good it returns output in green and if it doesn’t you get red output.  There are a lot of great use cases for Pester, like using it to ensure your code does what it’s supposed to, using it to validate your SQL Server environment (dbachecks), or in this example using it to make sure your demos are setup and ready to go.

When I’m preparing for a presentation I go through the demos over and over again, so it’s easy to accidentally leave things in a state that will cause issues when I go to do my demos in the presentation. If you’re creating a table, for example, during the demo and you already created it practicing and then forgot to drop it, the demo gods will strike and it’ll fail when it matters most! A simple Pester test to check whether the table exists will solve this issue.

Even if you aren’t giving talks in public (or inside your company), Pester is a useful tool for ensuring that the thing you expect to be the case actually is the case.

Comments closed

Setting Up a SQL Server Lab with AutomatedLab

Jess Pomfret looks at a very interesting Powershell module:

There is a fantastic PowerShell module called AutomatedLab that can enable you to easily build out a lab for the  specific scenario you need to test. Even better is the module comes with 70 sample scripts that you can start with and adapt to meet your needs.

The module gives you the option to work with Hyper-V or VMWare. I will say most of the examples are using Hyper-V, and that is what I’ll be using also.

For my lab I want a SQL Server 2019 instance joined to a domain, and a separate client machine that I can manage the SQL Server from. On the client I would need to be able to connect to the internet as I want to be able to download PowerShell modules from the gallery easily.

It’s about time for me to rebuild my lab, so I’ll need to check that out.

Comments closed

Creating an Elastic Jobs Agent

Kate Smith continues a series on elastic jobs in Azure SQL Database:

Having laid the conceptual groundwork for Elastic Jobs in two previous postings (12), I am now going to create an elastic job and associated credentials using PowerShell.  For this scenario, I have one or more databases with a table ‘T’ and statistics ‘tStats’. I want to enforce an update for these statistics every day. To do this, I need to check that my stats have been updated in the past day, and if not, update them. The T-SQL to update statistics on a table “T” with stats named “tStats” is simple:

Click through for the Powershell script.

Comments closed

Searching for Functions in dbatools

Andy Levy wants to find a function in dbatools:

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.

Read on to see how it works and a couple of examples.

Comments closed

Displaying a Toast with Powershell

Jess Pomfret has some fun with dbatools and the BurntToast Powershell module:

I have just a quick tip for you today using the BurntToast module to notify us when a backup is complete. As DBAs there is always plenty to do, so we don’t want to have to sit and watch a long running script to catch the moment when it finishes.  Usually what happens to me is I kick off the script, move on to something else and then totally forget about it, perhaps until someone asks if it’s done yet. Oops. Well this tip will help avoid that.

Best used sparingly, but quite helpful when you’re working on a long-running task and need to get back to Spider Solitaire work.

Comments closed