Press "Enter" to skip to content

Category: Powershell

Cmdlets vs Functions in Powershell

Published 2024-11-11 by Kevin Feasel

Adam Bertram differentiates the two:

Whenever you execute or run anything in PowerShell, you’re running a “command.” A command is an informal term for executable code, which can be a built-in PowerShell cmdlet, a custom function, or even an object method.

Cmdlets are compiled executable code provided by Microsoft, created in a language like C#, compiled, and added to a PowerShell module by software developers. Functions, on the other hand, are created directly in PowerShell by users. Although functions and cmdlets are technically different, they can be thought of as similar when you’re starting out.

Read on for more information, including a demo. I normally call them all cmdlets, but apparently I’ve been doing it wrong all this time.

Leave a Comment

Converting Audit Files to CSV via Read-SqlXEvent

Published 2024-10-16 by Kevin Feasel

Patrick Keisler finds a work-around:

Reading the contents of an audit event file can be accomplished using either Management Studio or the T-SQL function, sys.fn_get_audit_file. However, sometimes a customer may want to use a third-party tool to read and aggregate audit records, and some of those tools do not have the ability to read the binary audit file. In that case, the customer will just use sys.fn_get_audit_file to write the audit records to a database and then use the third-party tool to ingest those records from the database.

What if that third-party tool cannot even read from SQL Server? That happened to me recently where the only option was to read from a text-based file.

Read on for Patrick’s solution to the problem and a real pain point you’ll find along the way.

Comments closed

Parameterized Queries with dbatools

Published 2024-10-11 by Kevin Feasel

Thom Andrews builds a query:

Many of us are likely aware of PowerShell, even if we don’t use it too frequently, and I suspect that if you’re reading this post you’re also familar with things like sqlcmd. Hopefully, you have also heard of DbaTools, a module for PowerShell (and if you haven’t, hopefully that’s why you’re here). Today, I wanted to discuss running parametrised queries (including table type parameters) from PowerShell, which is notorious hard/impossible with sqlcmd (or invoke-SqlCmd), using the DbaTools module.

Click through for examples building up from zero parameters up to a table of parameters.

Comments closed

Fixing Missing SQL Agent Jobs Post-Migration

Published 2024-10-08 by Kevin Feasel

Lee Markum is looking for that lost shaker of SQL Agent jobs:

I’ve been doing migrations fairly continuously for the past 18 months. PowerShell has been my primary mechanism for many parts of the process, including copying jobs from the source SQL Server to the target. That has worked almost without incident each time. However, recently, an app team noticed that there were SQL Server Agent jobs missing on their new 2022 SQL Servers.  Because the first couple of missing jobs also existed on their Development environment, they were able to recreate those jobs in production. They naturally expressed concern that other jobs may be missing.

Read on for Lee’s process, including the solution.

Comments closed

Test those Backups

Published 2024-10-01 by Kevin Feasel

Grant Fritchey provides an important public service announcement:

Please, let me reiterate: The only valid test of a backup is a restore.

THE

ONLY

VALID

TEST

OF

A

BACKUP

IS

A

RESTORE.

Grant is absolutely correct here. And do you know how hard it is to test a SQL Server backup? With dbatools, it’s this hard: Test-DbaLastBackup. That one cmdlet restores a backup (under a different name so it doesn’t clobber your existing database), runs DBCC CHECKDB against the restored data, drops the test database after the fact, and even allows you to do all of that against a different SQL Server instance.

Incidentally, my next YouTube series just happens to be around backup and restoration in SQL Server, so stay tuned if you’re interested.

Comments closed

Parsing SQL Server Query Stats in Powershell

Published 2024-09-24 by Kevin Feasel

Andy Levy builds a cmdlet:

A couple weeks ago, DrData asked

With SET STATISTICS IO ON, is there really no way to show the TOTALS at the bottom? There are some nice tools like StatisticsParser but it sure would be nice to see the totals of all values right at the bottom, especially when there are many tables.

The task at hand in the office was a need to do the same thing, but with SET STATISTICS TIME ON. So I got to thinking…I can PowerShell my way through this, right?

Andy, being The Man, shows us exactly how to do this.

Comments closed