Press "Enter" to skip to content

Category: Powershell

Three Years of PSBlitz

Vlad Drumea looks back:

In case you’re not familiar with it, PSBlitz is a PowerShell script that outputs SQL Server health and performance diagnostics data to either Excel or HTML, and saves execution plans and deadlock graphs as .sqlplan and .xdl files.

It’s based on modified non-stored procedure versions of Brent Ozar’s SQL Server First Responder Kit and Erik Darling’s sp_QuickieStore.
Along with some other custom scripts I’ve put together.

You can get more details about its capabilities and usage from my PSBlitz GitHub repo.

Read on to learn about the origin story of PSBlitz, some of the major changes that have taken place over the past three years, and a bit more on the tool.

Leave a Comment

A Setup for Powershell

Vlad Drumea shows off one way to get work done in Powershell:

This is a brief post containing my information about my PowerShell coding setup, IDE, extensions, etc.

I’m writing this mainly because, based on the reactions to my reddit comment, some people might not be aware of how helpful the VS Code + PS extension combo can be for PowerShell code writing.

This is the environment that I use to write PowerShell scripts such as PSBlitzDecode-AsusRouterConfig, and my SQL Server install script.

Click through to see how Vlad gets it done. Every time I see someone using the Powershell ISE, I feel the urge to stage an intervention, as the ISE does some really weird stuff with your Powershell environment that doesn’t make it amenable to writing good scripts. This is a much better way.

Leave a Comment

Loading CSV Files via dbatools

David Seis loads some data:

Most businesses are rotten with Excel sheets and CSV exports from various tools and as my mentor puts it “Excel is the world’s database”. The dbatools command Import-DbaCsv enables the quick load of CSV tables into SQL Server, which then opens up the world of fast transformation and use in other tools such as PowerBI, or even just having it queryable rather than just in a file somewhere.

Import-DbaCsv is for you if any part of your job is to manually manage CSV files and you want to reduce the amount of time that you take to do that processing. Even if you are not a DBA but are interested in the data field, you can use SQL Server express for free, import and transform data using PowerShell for free, and export that data into Power BI or any other tool for free. I promise it can make your manual processes better, faster, and more resilient and save you tons of time!

Read on for a review of the quite useful cmdlet.

Leave a Comment

Ordering Collections in Powershell

Shane O’Neill demands order:

However, there is one drawback I have with hash tables: they don’t default to the order in which they are inserted. I’m OK with this since I come from a DB background, and I’m used to order not being enforced unless I specify an ORDER BY. Not everyone is as lenient as we are, though, and the vast majority of the louder masses expect this ordering.

Now, there are ways around this unordered aspect of hash tables.

Click through for the easy answer, the less easy answer, and some additional thoughts on script development from Shane.

Leave a Comment

Installing SQL Server CUs via dbatools

David Seis updates an instance or thirty:

Keeping your SQL Server instance up to date with the latest service packs and cumulative updates is one of the key components of SQL Server security. In this blog post, we will audit the dbatools command Update-DbaInstance, which can save you hours every month and help keep your environment more secure.

I will test, review, and evaluate the script based on a series of identical steps. Our goal is to provide insights, warnings, and recommendations to help you use this script effectively and safely. Update-DbaInstance is powerful tool to automate the update of one or many SQL server instances without needing to log in.

Click through for David’s thoughts on the cmdlet.

Leave a Comment

Bad Request Error Running Powershell in Azure DevOps

Koen Verbeeck wants good requests:

I needed to run a PowerShell cmdlet in an Azure Devops pipeline. The cmdlet in question was New-AzRoleAssignment, but the cmdlet itself isn’t important. What is important is that I needed to pass the object ID of a service principal to the command. Even though I was pretty sure the syntax and everything was correct, I got a “Operation returned an invalid status code ‘BadRequest’” error when the PowerShell was run (inside an Azure PowerShell task):

Read on to see how Koen diagnosed and resolved the issue.

Comments closed

Adding Timeouts to Pester Tests

Adam Bertram runs out of time:

Have you ever had a Pester test hang indefinitely, blocking your entire test suite? Maybe it’s waiting for a network response that never comes, or stuck in an infinite loop. Without proper timeout handling, one bad test can ruin your entire CI/CD pipeline.

In this article, you’ll learn how to implement robust timeout handling for Pester tests using PowerShell runspaces, ensuring your test suite always completes in a predictable timeframe.

Click through for the code and explanation.

Comments closed

Copying Data in dbatools

Haripriya Naidu makes a copy:

If you want to copy huge data from one SQL server to another, try using dbatools which has powershell module underneath.

In the demo here, I’ve compared 2 dbatools commands to move data from one SQL server to another:

Write-DbaDbTableData vs Copy-DbaDbTableData

Click through to see which one wins the speed challenge.

Comments closed

Testing Database Backups with dbatools

David Seis performs a test:

In this blog post, we will audit the dbatools command Test-DbaLastBackup. I will test, review, and evaluate the script based on a series of identical steps. Our goal is to provide insights, warnings, and recommendations to help you use this script effectively and safely. Test-DbaLastBackup is a powerful tool to automate the validation of a backup. Backup testing should be a regular part of SQL Server maintenance and this command works well in automating this task.

Click through for a dive into the cmdlet. I’m a big fan of this cmdlet because it drastically simplifies the process of ensuring that your backups are good.

Comments closed

The FabricTools Powershell Module

Kamil Nowinski has a module for us:

In the world of Microsoft Fabric, DevOps is still maturing. Unlike Azure Data Factory (ADF), which has been around long enough to have established tooling – like the #ADFTools I developed 5 years ago – Fabric is new, broad, and complex, in a very positive way!

Microsoft Fabric integrates data engineering, warehousing, real-time analytics, and BI. With this scale, the need for solid DevOps tooling is more critical than ever.

Click through to read a little bit of the history behind the project, as well as what’s currently available. And it’s all free and open-source.

Comments closed