Press "Enter" to skip to content

Category: Powershell

Using Show-Command in Powershell

Published 2019-07-12 by Kevin Feasel

Kenneth Fisher walks through the Show-Command cmdlet in Powershell:

Years ago I blogged about how I like to use the SSMS scripting feature to learn how to do things. Well now I’m starting to learn Powershell and it turns out there is a GUI here as well that will help me learn to script. At least in a very basic way. For example, if I want to see what parameters are actually available for Get-Help and maybe script out a template to work with then I can do this:

Show-Command Get-Help

Read on to see how you can put this into action.

Comments closed

dbatools and Linux

Published 2019-07-10 by Kevin Feasel

Chrissy LeMaire takes us through dbatools support on Linux:

As a long-time Linux user and open-source advocate, I was beyond excited when PowerShell and SQL Server came to Linux.

A few of the decisions I made about dbatools were actually inspired by Linux. For instance, when dbatools was initially released, it was GNU GPL licensed, which is the same license as the Linux kernel (we’ve since re-licensed under the more permissive MIT). In addition, dbatools’ all-lower-case naming convention was also inspired by Linux, as most commands executed within Linux are in lower-case and a number of projects use the lower-case naming convention as well.

Considering how many OS-specific operations there are, the percentage of Powershell commands in dbatools which work is excellent.

Comments closed

Pester and Testable Powershell Scripts

Published 2019-07-09 by Kevin Feasel

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.

Comments closed

RegEx Capture Names in Powershell

Published 2019-07-08 by Kevin Feasel

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.

Comments closed

Estimating Backup Footprint

Published 2019-07-08 by Kevin Feasel

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.

Comments closed

dbatools and Error Handling

Published 2019-07-02 by Kevin Feasel

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.

Comments closed

dbatools 1.0 Available

Published 2019-06-20 by Kevin Feasel

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.

Comments closed

Generating Scripts from SSMS

Published 2019-06-03 by Kevin Feasel

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.

Comments closed

Getting Version Info From dbatools

Published 2019-05-31 by Kevin Feasel

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.

Comments closed

Making SpeedPASSes Better

Published 2019-05-23 by Kevin Feasel

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.

Comments closed