Press "Enter" to skip to content

Category: Powershell

Altering Job Steps With Powershell

Published 2017-02-14 by Kevin Feasel

Rob Sewell uses Powershell to modify hundreds SQL Agent job steps on hundreds of SQL Server instances:

We will use the sqlserver module, so you will need to have installed the latest version of SSMS from https://sqlps.io/dl

This code was run using PowerShell version 5 and will not work on Powershell version 3 or lower as it uses the where method.

Lets grab all of our jobs on the estate. (You will need to fill the $Servers variable with the names of your instances, maybe from a database or CMS or a text file)

One oddity with SQL Agent jobs is that you absolutely need to call the Alter() method at the end or else the changes will not actually take effect.

Comments closed

Searching For Powershell Functions In Scripts

Published 2017-02-14 by Kevin Feasel

Stuart Moore has a regex which looks for Powershell cmdlets used in a script:

Having had a quick bingle around for a prewritten regex example I didn’t come up with much that fitted the bill. So in the hope that this will help the next person trying to do this here they are:

Assumptions:

  • A PowerShell function name is of the form Word-Word

  • A PowerShell function definition is of the form “Function Word-Word”

  • A Powershell function call can be preceeded by a ‘|’,'(‘, or ‘ ‘

  • The script is written using a reasonable style, so there is a ‘ ‘ post call

Click through for the script.

Comments closed

Using Powershell To Shred Query Plan XML

Published 2017-02-02 by Kevin Feasel

Mike Fal shows how to use Powershell (or any .NET language) to read parts of a query plan:

Once the pattern is down, the use is pretty straightforward. There’s also more options accessible to you. If we just look at the RunTimeCountersPerThread node, we can compare other values such as Rows, Scans, and CPU time. We could really get crazy and extract all the different statements within the batch. There are numerous possibilities for analysis and review.

I’m not here to tell you that you should start using PowerShell to automate query tuning. Query performance is an art form and requires a lot of case-by-case analysis. However, like any great carpenter, it’s good to know the capabilities of your tool set. Understanding the options available to you not only helps you be more effective, but can also provide answers you may not have had access to.

It’s another tool for the belt.

Comments closed

Powershell Defaults

Published 2017-02-01 by Kevin Feasel

Michael Sorens has some time-saving defaults for Powershell:

Besides setting up some convenient shortcuts for use with built-in cmdlets, for developers it is also handy to be able to work some magic with your own custom cmdlets. In my shop, for example, we have one module containing a couple dozen cmdlets, many of which use a common parameter, Mode. This Mode parameter varies from client to client, but for any single client, every cmdlet working on their data needs to use the same value of Mode. So I have to add -Mode hist-0010-dev.test onto each cmdlet I am using, which gets very tiring/annoying very quickly. You could, of course, put that value into a variable and then just use, e .g. -Mode $myMode, which is less typing, but if less is better, then no typing at all is better still.

There are a lot of tips in this article, so take some time with it.

Comments closed

Powershell On Windows 10 Bash

Published 2017-01-27 by Kevin Feasel

Max Trinidad goes all the way with installing an Ubuntu environment on Windows:

It’s a known fact, if you install PowerShell Open Source in Windows 10 Bash subsystem, that it won’t work correctly. As soon as start typing $PSVersionTable and press enter, the cursor goes to the top of the screen. And, you keep typing and it gets very ugly.

Now, what if I tell you, I found the way to run PowerShell Open Source without any of these issues. Just like running it like it was installed in a Linux environment. No issues with the cursor going crazy and able to page up and down.

There are quite a few steps here, but Max lays them out clearly.

Comments closed

Querying A Named Instance From Powershell

Published 2017-01-26 by Kevin Feasel

Steve Jones explains how to query a named instance in Powershell:

I was looking at some sample code the other day and it looked like this.

cd sqlserver:\sql\localhost\default\databases

This allows you to browse the list of databases on your local instance. However, this is for a default instance, which I don’t have on this host. How can I get to a named instance? Usually I connect as .\SQL2016, so where does that fit in PowerShell?

Read on for the answer.

Comments closed

Restoring A Database To A Different Location

Published 2017-01-26 by Kevin Feasel

Mike Fal shows how to restore a database to a different location using Powershell:

The cmdlet is straightforward in its use. Fundamentally, all we need to declare is an instance, database name, and backup file. However, if we don’t declare anything else, the cmdlet will try and restore the database files to their original locations. Keep in mind this is no different than how a normal RESTORE DATABASE command works.

This is where we make our lives easier with PowerShell. First off, to move files using Restore-SqlDatabase, we need to create a collection of RelocateFile objects. Don’t let the .Net-ness of this freak you out. All we’re doing is creating something that has the logical file name and the new physical file name. In other words, it’s just an abstraction of the MOVE statement in RESTORE DATABASE.

Read the whole thing.

Comments closed

Installing DBATools Without Powershell 5

Published 2017-01-24 by Kevin Feasel

Steve Jones has to install dbatools without the benefit of Powershell 5.0:

I have a number of Windows Server 2012 R2 VMs. I need to get rid of these and upgrade to WS2016, since installing SQL 2016 is a pain on these VMs. However, for the time being, I’m stuck with them.

I was looking to try some of the dbatools modules. I popped open an elevated command prompt and [error]

This is because that module isn’t in this version of PoSh. I checked, and I could see this is v4, and Install-Module was added in v5.

Click through for the solution.

Comments closed

Streaming Data To Power BI

Published 2017-01-23 by Kevin Feasel

Sacha Tomey builds a quick Powershell script to feed streaming data into Power BI:

Peter showed off three mechanisms for streaming data to a real time dashboard:

  • The Power BI Rest API
  • Azure Stream Analytics
  • Streaming Datasets

We’ve done a fair bit at Adatis with the first two and whilst I was aware of the August 2016 feature, Streaming Datasets I’d never got round to looking at them in depth. Now, having seen them in action I wish I had – they are much quicker to set up than the other two options and require little to no development effort to get going – pretty good for demo scenarios or when you want to get something streaming pretty quickly at low cost.

Click through for more details and a sample script.

Comments closed