Pester and Testable Powershell Scripts

Shane O’Neill has a public service announcement:

Now scripts are notoriously hard to test, I’ve written about how I’ve done that before but, honestly, if you really want to know then you need to check out Jakub Jares ( blog | twitter ).

Knowing how difficult testing scripts are, the first thing I decided to do was take the functions in the script and split them out. This way they can be abstracted away and tested safely.

I also didn’t want to take on too much at one time so I choose a random function, GetPreviousTag, and only actioned that one first.

Shane also found a bug in the first script, underscoring the importance of good tests.

RegEx Capture Names in Powershell

Jeffery Hicks shows how you can use regular expressions to create capture names in Powershell:

Once you have matching patterns, you can define them with a name. The general layout looks like this:

(?<capture-name>Your-Pattern)

The parentheses are key. My datetime pattern can be defined as a named capture:

(?<date>\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}Z)

Click through to see how you can combine these and turn them into Powershell objects.

Estimating Backup Footprint

John Morehouse has a Powershell script to estimate how much disk space you’re using with backups:

Recently, I needed to be able to determine the total size for all the most recent full backups for certain servers. Luckily for me these particular servers were using Ola Hallengren’s Maintenance Solution which simplified things since each back up had it’s own specific directory. Powershell has the flexibility to easily provide what I need with little effort.

Let’s take a look!!

John shows specifically the latest full backup, but you can extend this to include other sets as well.

dbatools and Error Handling

Shane O’Neill takes us through some of the error-handling dynamics available in dbatools:

PowerShell errors normally contain useful information on what went wrong. With this information, the “what went wrong” can be fixed.

That being said, if you are writing PowerShell scripts and not raising valid error messages then I highly advise you to go back and fix that.

dbatools raises these error messages as friendly warning messages since we’ve found people will read a warning message quicker than they will read an error message.

There are several options available for you to handle errors, including viewing them as warnings, viewing as errors, and populating error text in variables.

dbatools 1.0 Available

Chrissy LeMaire and a cast of thousands have officially released dbatools 1.0:

We are so super excited to announce that after 5 long years, dbatools 1.0 is publicly available!

Our team had some lofty goals and met a vast majority of them . In the end, my personal goal for dbatools 1.0 was to have a tool that is not only useful and fun to use but trusted and stable as well. Mission accomplished: over the years, hundreds of thousands of people have used dbatools and dbatools is even recommended by Microsoft.

Go forth and update.

Generating Scripts from SSMS

Jeff Mlakar shows how you can use Management Studio to generate scripts for database objects:

Sales.SalesOrderDetail looks like a good choice. Let’s generate a script for that table, all associated objects, and its data.

The safest way to create structure including all indexes, keys, defaults, constraints, dependencies, triggers, etc. is to use SSMS Generate Scripts.

I would also recommend becoming familiar with the Powershell command to generate scripts and what dbatools has on store.

Getting Version Info From dbatools

Jess Pomfret shows how you can get your operating system and SQL Server versions from the dbatools Powershell module:

With these dates on the horizon it’s a good time to look at our estate and make sure we have a good understanding of the versions we currently support. I’m going to show you how to do that easily with a couple of dbatools functions. Then, bonus content, I’ll show you how to present it for your managers with one of my other favourite PowerShell modules ImportExcel.

Jess gets bonus points for avoiding the dreaded pie chart at the end.

Making SpeedPASSes Better

Wayne Sheffield shares some useful tips for making the SpeedPASS experience better:

As things were wrapping up for our event last year, there was an important change made at the SQLSaturday site for dealing with SpeedPASSes. Previously, the admission ticket was sized differently from everything else, which created hassles in cutting and in using perforated paper (and why there were custom-printed SpeedPASSes – so that we could make them fit). However, the change was that all of the labels are now the same size. This means that perforated paper can now be used to print out the SpeedPASS PDF files that are generated at the SQLSaturday site.

Additionally, I was looking for ways to automate some of the manual process of merging the PDF files as described in the previous post, especially with the work necessary in the Excel spreadsheet. The end result is that we now have a new process that we used, and I’m sharing it with everyone to help them out.

Read on for tips including perforated paper and Powershell scripts tying it all together.

dbatools 1.0 Forthcoming

Chrissy LeMaire announces that dbatools will be out on June 19th by my count:

We’ve got about 30 issues left to resolve which you can see and follow on our GitHub Projects page. If you’ve ever been interested in helping, now is the perfect time as we only have 30 more days left to reach our goal.

If you’re a current or past dbatools developer, we’d love any help we can get. Just hit up the GitHub Projects page to see what issues are left to resolve. If someone is already assigned, please reach out to them on Slack in the #dbatools-dev channel and see if they can use your help.

Read the whole thing and see if there’s anything you can do to help.

Multi-Line Powershell Comments

Jess Pomfret shows how you can build out Powershell comments with multiple lines of code in them:

You can see above the first example looks good, however in the second example the first two lines should both have a prompt to show they are code. I spent a little while Googling this without much avail. I then figured, somewhere within dbatools there must be an example with two lines of code. Sure enough I found my answer, and it’s pretty straightforward.

Click through for the answer, as well as one of the most important Powershell cmdlets you’ll ever find on the Internet.

Categories

August 2019
MTWTFSS
« Jul  
 1234
567891011
12131415161718
19202122232425
262728293031