Category: Powershell

Tom Rayner has an example of building multiple regex matches in Powershell:

In the PowerShell Slack, I recently answered a question along these lines. Say you have a string that reads “first thing {} second thing {}” and you want to get to “first thing {0} second thing {1}” so that you can use the -f  operator to insert values into those spots. For instance…

1 2	“first thing {0} second thing {1}” –f $(get–date –format yyyy–MM–dd), $(get–random) # Will return “first thing 2018-01-10 second thing <a random number>”

The question is: how can you replace the {}’s in the string to {<current number>}?

Read on for more details.

Andrew Pruski has written a quick comparison script to check if two instances have the same set of databases or the same configuration settings:

For instance, last week I was working with two instances that contained databases part of an Always On availability group and needed to ensure that all databases on the primary were on the secondary.

Now I know there’s a few ways to do this but I needed a quick and easy method as there were over 300 dbs involved.

The method I used to do this implemented the powershell cmdlet Compare-Object

What this does is pretty much what it says on the tin. The cmdlet takes two objects and compares them based on a input property (in this case it’ll be database name).

This can turn into a much more complicated comparison, but Andrew shows that the basic concept is quite straightforward.

Mike Robbins notes that the Compare-Object cmdlet in Powershell does not like to handle null values:

I thought I’d run into a bug with the Compare-Object cmdlet in PowerShell version 5.1 earlier today.

1 2 3

$DriveLetters = (Get-Volume).DriveLetter $DriveLetters Compare-Object -ReferenceObject $DriveLetters -DifferenceObject $DriveLetters

Compare-Object : Cannot bind argument to parameter ‘ReferenceObject’ because it is null.
At line:1 char:33
+ Compare-Object -ReferenceObject $DriveLetters -DifferenceObject $Driv …
+ ~~~~~~~~~~~~~
+ CategoryInfo : InvalidData: (:) [Compare-Object], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.CompareObjectCommand

Read on for the description of the problem as well as the solution.

Kenneth Fisher shows how to generate music with Powershell:

Happy holidays everyone! Ok, this isn’t even remotely related to SQL Server but it sounded fun. I found someone playing music using Powershell (I lost the link, sorry) and thought Christmas Music!

Not the most efficient way to generate music, but it’ll do.

Jason Squires has an example of combining dbatools with Pester to build out SQL Server instance quality reports:

With what I have learned from Pester, it seems to be best to test each command on its own. That got me to thinking, what if I walk into a large environment and I want to see an enterprise summary of best practices? I started working on a few different versions with the ultimate goal of using dbatools function Get-DbaRegisteredServer. I have included a few examples of Test functions that include best practices, as well as some default database best practices all from dbatools.

In my first run, I wanted to make sure that I can pass multiple servers with multiple tests and came up with the below script. You will notice the top section that uses the Get-Dba functions, I had to supply my own values of the properties I am testing. In the bottom section, you will notice that dbatools Test-Dba functions have the IsBestPractice property already and that is all you have to test against.

Read on for a sample of what you can do.