Press "Enter" to skip to content

Category: Powershell

dbatools: The Swiss Army Knife For DBAs

Published 2018-04-12 by Kevin Feasel

Jess Pomfret uses this T-SQL Tuesday to cover some of her favorite cmdlets in dbatools:

Test-DbaSqlBuild

When I found this command I couldn’t have been more excited. My day-to-day job requires the care and watering of over 100 SQL Server instances of varying versions.  Using this command you can get the current build of all your instances and then compare that to the most recent available.  There are also parameters for how far you want to be from the latest version. Setting the -latest switch means just that, your server will only be seen as compliant if it’s on the latest release, passing in -1CU means that it can be no more than 1 cumulative update behind.

Read on for a few additional useful cmdlets.  Out of a large number of useful cmdlets.

Comments closed

SQL Server Powershell Module On PowerShell 6 Core

Published 2018-04-11 by Kevin Feasel

Drew Furgiuele is ready to retire to his fainting couch:

So, I bit: the tweet he referenced was announcing a new version of the SQL Server module (21.0.17240). Here’s a quick list of the updates included:

  • Added Get-SqlBackupHistory cmdlet
  • Ported PS Provider to .NET Core for PowerShell 6 support 
  • Ported a subset of cmdlets to .NET Core for PowerShell 6 support 
  • Powershell 6 support on macOS and Linux in Preview. 
  • To use SqlServer provider on macOS and Linux mount it using a new PSDrive. Examples in documentation.
  • Removed restriction of 64-bit OS for this module. Note: Invoke-Sqlcmd cmdlet is the only cmdlet not supported on 32-bit OS.

The bold lines are my emphasis: with PowerShell 6 support for Linux and macOS, that opens up new avenues for connecting to and automating SQL Server from any platform. This is exciting stuff. I couldn’t wait to take it for a spin, so I set up a quick demo environment to test it out.

It’s not perfect but it did give Drew the vapors, which is a good sign that they’re on the right track.

Comments closed

Collecting Login Details With dbatools

Published 2018-04-11 by Kevin Feasel

Chrissy LeMaire shows us several ways to track who has connected to your SQL Server instance:

Using the default trace is pretty lightweight and backwards compatible. While I generally try to avoid traces, I like this method because it doesn’t require remote access, it works on older SQL instances, it’s accurate and reading from the trace isn’t as CPU-intensive as it would be with an Extended Event.

Click through for details on this as well as three other methods, along with the dbatools glue to make it work.

Comments closed

New dbachecks Checks

Published 2018-04-10 by Kevin Feasel

Rob Sewell announces updates in the dbachecks Powershell package:

Today we updated the HADR tests to add the capability to test multiple availability groups and fix a couple of bugs

Once you have installed dbachecks you will need to set some configuration so that you can perform the tests. You can see all of the configuration items and their values using

Read on for more about these updates.

Comments closed

Powershell Splats And Quotes

Published 2018-04-06 by Kevin Feasel

Shane O’Neill shows when you need to use single quotes when splatting in Powershell:

If you’ve checked the examples in that post – and I recomment that you do – then you’ll see that it takes the syntax of Parameter = 'Value'.

Notice the Parameter portion is not in quotes? It also works perfectly well if you have the Parameter name in quotes e.g. 'Parameter' = 'Value' (double quotes works too).

Why would you use one instead of the other?

There is a special circle in the Inferno for people who put spaces in their parameter names.

Comments closed

Powershell: CSVs And NoTypeInformation

Published 2018-04-06 by Kevin Feasel

Dan Blank lays down some information about saving headerless CSV files in Powershell:

Anyway, a question was posted recently using the #pshelp hashtag on Twitter.  How do you replace the header line of multiple csv files at once?

I saw this and maybe cockily thought to myself, “that’s got to be an easy 2 liner, bet I can boost my ego and quickly write out a solution”.

Spoilers:  it turned out to be slightly more complex.

Comments closed

Finding SQL Server Instances With dbatools

Published 2018-03-28 by Kevin Feasel

Chrissy LeMaire shows off a very helpful command in dbatools:

Nearly every time I inherit a SQL Server environment, I’m only given a partial list of SQL Servers that exist on the network. It’s my usual routine to get permission to sniff the network then run about five different programs including Idera’s SQL Discovery and Microsoft’s SQL Server Assessment and Planning Toolkit.

I always thought it’d be cool to have one comprehensive PowerShell command that could do the work of all the above and was ecstatic to see NetSPI’s Scott Sutherland had written a few commands to do just that in his awesome PowerShell module PowerUpSQL.

When I saw Scott’s multi-pronged approach (including some UDP magic 🎩), I asked if he’d be interested in contributing to dbatools and he said yes! He submitted a gorgeous mock-up and I was so excited. Then came the PR, complete with great documentation and multithreading.

Click through for a lot more information on the command.

Comments closed

Resuming Azure SQL Data Warehouse With Powershell

Published 2018-03-28 by Kevin Feasel

Arun Sirpal shows how to unpause an Azure SQL Data Warehouse instance using Powershell:

I totally forgot that with Azure SQL DWH you can pause and resume compute, to save money because it is expensive. Question is how do you go about resuming compute? TSQL is not possible and sure you can do the change via Azure portal but what about PowerShell?

This makes it easy to script out an overnight data load and then pausing the Azure Data Warehouse until the morning when those analysts come in, so that you can save a bit of cash (or a lot, depending upon your DWU utilization).

Comments closed

Joining Objects In Powershell

Published 2018-03-23 by Kevin Feasel

Shane O’Neill makes a discovery:

…if there is enough data to import this into the database & use T-SQL then you can bet that’s what I’m going to do! It’s what it was designed for, I’d find it easier, and it’s probably going to be faster after you hit a certain threshold.

However, if it’s small sets and the effort of importing the data is going to slow you down and break your flow…

Well, that doesn’t have to be the case anymore.

Read on to see joins in action.

Comments closed

Collecting PRINT Outputs From Powershell

Published 2018-03-20 by Kevin Feasel

Jana Sattainathan shows how to query a number of SQL Server instances in parallel using Powershell and collecting the PRINT outputs from each:

As an example, you may have a block of SQL that PRINTs out the current privileges in the databasethat can then be saved off and used as an independent script.

In my case today, I need to collect information on the Service Pack and Cumulative Updates that need to be installed/applied to 200+ SQL Server instances. MS provides the SQL script to identify the right SP and CU to install. Please make sure you download the zip file and unzip the long SQL. I need to run this SQL against the instances to get the info. However, the information returned is completely with PRINT statements!

Click through to see how Jana did it.

Comments closed