Press "Enter" to skip to content

Category: Powershell

Running Diagnostic Notebooks via Powershell

Published 2022-07-27 by Kevin Feasel

Tracy Boggiano kicks off a notebook:

As part of starting a new job you need a way to get a good inventory of basic information of SQL Server instances.  Once you have done what I outlined in this blog post.  I find it helpful to run Glenn Alan Berry’s Diagnostic Notebooks against all the instances to get a static point in time snapshot of all the properties and some performance information.  While dbatools has commands under the Community Tools section for running the data into spreadsheets and creating notebooks for the newest queries I like to go get Glenn’s because he has all the comments in there of what the mean and links to resources about things.  So you can explore that route if you like but I’ll be manually downloading them from Glenn’s site for that reason.  To able to open the notebooks successfully in ADS look for the tip on my blog post on Tools I Use on My Jumpbox for opening large notebooks.

Click through for a script Tracy uses to kick off the notebook regardless of the SQL Server version.

Comments closed

Write Powershell Output to a File

Published 2022-07-19 by Kevin Feasel

Kenneth Fisher needs someplace to put all of his great ideas:

Last week I posted about a request to get a list of services. The first problem I ran into was expanding the column width, now I need to get the output off my screen and into a file to send the requestor. The cmdlet out-file is perfect for this and it’s pretty simple.

Click through to see how the Out-File cmdlet works as well as another cmdlet you may wish to use instead.

Comments closed

Working with Objects in Powershell

Published 2022-07-11 by Kevin Feasel

Jeffrey Hicks explains the value of working with objects in Powershell:

I expect I will write several articles about PowerShell and its relationship with objects. I know that this is the biggest hurdle for PowerShell beginners to overcome. But once they grasp that PowerShell is about working with objects in the pipeline, they recognize the value and begin finding it easier to write PowerShell code and use it interactively at a console prompt.

Read the whole thing and if you like what you see, there’s also a Substack where you can sign up for free or subscribe for additional content.

Comments closed

Uploading Multiple Reports to Power BI

Published 2022-07-11 by Kevin Feasel

Jon Fletcher doesn’t have time to upload reports one by one with the UI:

In this blog post, I will be sharing a PowerShell script that allows multiple Power BI reports to be uploaded at once. In a previous blogpost, I shared a PowerShell script that allowed users to downloaded multiple Power BI reports. Combined you could move several reports from one workspace to another in a few seconds.

The script is downloadable at the bottom of the page as a txt file. To use the script there are three steps to take.

Click through to see how it all works.

Comments closed

Expanding Column Width in Powershell Results

Published 2022-07-08 by Kevin Feasel

Kenneth Fisher supersizes the screen:

Notice the ellipsis (the three dots). That’s showing us that the name was too long and ended up being truncated. Given that I’ve been doing this for a little while now I’m almost completely certain that if I send this as it is the users are going to want to know full names. And with my luck I’ll end up having to give them each truncated string individually. On the theory that if I have time to do it twice I probably have time to do it right the first time, let’s figure out how to expand the columns. Fortunately, as with most things Powershell, there’s a cmdlet for that.

Read on to see what the process looks like.

Comments closed

Refreshing Power BI Usage Metrics Report Credentials

Published 2022-07-07 by Kevin Feasel

Stephanie Bruno fixes a credentials issue:

Have you ever had a Usage Metrics Report just stop refreshing? At the bottom you’ll see a little message that tells you to check the credentials. But you don’t have access to the Usage Metrics Report dataset in the list of datasets in the workspace because it’s hidden! So even if you wanted to delete it and nicely ask it to start again, you can’t do it from the workspace. What can you do? Well, you can delete the Usage Metrics Report dataset with the Power BI Rest API.

Read on to see how.

Comments closed

Setting Breakpoints in Powershell Scripts

Published 2022-06-23 by Kevin Feasel

Patrick Gruenauer does a bit of debugging:

The Set-PSBreakPoint cmdlet sets a breakpoint in a script. When you are troubleshooting a script it could be helpful to know what’s going on in a particualar step or workflow. In this blog bost I will give you an overview and the basics you can build on to troubleshoot and investigate your script. Let’s jump in.

One of these years, I’m finally going to learn command-line debugging. I grew up in the IDE era and so never took the time to learn that skill.

Comments closed

Creating a Self-Signed Certificate in Powershell

Published 2022-06-21 by Kevin Feasel

Tom Collins says ‘trust me’:

Use the Powershell cmdlet New-SelfSignedCertificate

Warning: I only ever use this method for testing purposes – never use for Production environments. A typical test would be for  SQL Server Network Encryption SSL Certificate Management

Encryption strategy  should be part of a wider review of Database Security Countermeasures against hacker attacks

For more details check the Microsoft documentation but here are some notes on some of the parameters  attached to the Powershell cmdlet – New-SelfSignedCertificate.

Read on to see how you can create the certificate and use it in SQL Server. Connections will complain (and rightfully so) about the self-signed certificate but you’ll be able to try functionality which requires a certificate in an environment in which you don’t have one available for whatever reason.

Comments closed

Creating Calculation Groups in Power BI Desktop via Powershell

Published 2022-06-20 by Kevin Feasel

Phil Seamark helps those who are stuck lacking tools:

I recently shared on Twitter a 7-module learning path on MS Learn that teaches all you need to know about calculation groups in Power BI. This learning path is an excellent course, and I highly recommend it. However, as part of this exchange, I received a reply from someone lamenting that calculation groups are unusable in organisations that will not allow non-Microsoft applications. Power BI Desktop does not currently have UX enabling you to create/manage calculation groups in a Power BI Model, so the most common method today is to use 3rd party tools such as Tabular Editor.

This exchange is not the first time I have heard this feedback, so I decided to share a technique showing how you can use Microsoft tools. The approach used in this article uses PowerShell but can quickly get translated to VS Code or other scripting environments.

Click through if you’re in that unfortunate situation.

Comments closed

Searching for SQL Server Backup Locations

Published 2022-06-17 by Kevin Feasel

David Fowler can’t remember where those backups went:

Sometimes I find remembering where a particular server sends its backups to a nightmare.

You might have servers backing up to different locations, you might have different locations for individual databases and different locations for your fulls, diffs and logs. You might be trying to get your head around a customer’s set up, where the backups make no logical sense at all.

Whatever you’re up to, at some point, for some reason you’re going to need to access your backup location to get at the files.

Read on for a Powershell script which can help out with this task.

Comments closed