A Fine Slice Of SQL Server
Andy Levy checks if this is the end of the month:
This is one of those blog posts you write so that 2 years later, you can look it up to remind yourself how to do something.
I found myself needing to figure out if “today” was the end of the month in PowerShell. In T-SQL, this is easy, as we have the
EOMONTH()function. But PowerShell (the .NETSystem.DateTimestruct) doesn’t have the same thing.
Read on for the solution Andy came up with.
Leave a CommentDavid Seis looks at a powerful cmdlet in dbatools:
In this blog post, we will audit the dbatools command Invoke-DbaQuery. 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. Invoke-DbaQuery is the Swiss army knife of all dbatools commands as you can execute almost any T-SQL script you can think of via PowerShell.
Click through for an overview of what the cmdlet does, some tips on proper usage, and an important note around possible misuse.
Leave a CommentMike Robbins lays out an argument:
If you ever formatted a hash table in PowerShell, you know how easy it is to focus on function over form. But what if one minor formatting tweak could improve readability, reduce syntax errors, simplify code reviews, and enhance script maintainability? During a recent documentation update, I stumbled on a subtle but powerful practice—aligning the equals signs in hash tables. What began as a style suggestion proved to be a practical improvement that changed how I write PowerShell every day. Here’s why this seemingly minor change deserves a place in your scripting toolbox.
Click through to learn why. This doesn’t apply only to hash tables in Powershell, of course, so you could take this concrete example and extend it to other situations. As an example, this is a very common pattern for managing lengthy configuration files for the same reasons Mike points out. Just as long as your programming language is okay with extra whitespace around the equal sign (or equivalent), you can do this.
Leave a CommentJess Pomfret discovers a new flag:
Recently I was reading the docs for `Backup-DbaDatabase` and found a parameter I didn’t realise existed, but is so useful when you want to automate backups, but keep control of the file names.
Click through to learn more about the specific feature, as well as a reminder that it’s a good idea to read through the documentation. Not all documentation is good, but the work people have put into dbatools means that there is often a good example involving most of the available parameters. It turns out that there are, in fact, two examples that use -ReplaceInName in the documentation for Backup-DbaDatabase, so you get not only a description of the parameter but also two specific examples of how to use it.
Patrick Gruenauer catches ’em all:
Error handling is a critical aspect of writing robust scripts in any programming language. PowerShell provides a powerful structure for handling errors gracefully using
try,catch, andfinallyblocks. These constructs allows us to manage exceptions and ensure that important cleanup actions are performed, even when errors occur. In this blog post, I will show you how to use try catch finally in PowerShell. Let’s jump in.
Click through to see how the logic works in Powershell, as well as how you can read the exception itself in the catch block.
Comments closedJess Pomfret looks for old tables:
There are many reasons why you might end up with tables named
something_oldin your database. Perhaps this is part of your decommission strategy, to rename them to make sure they really aren’t in use. Or, it could be because you need to make schema changes, you can rename the current table and create a new table with the desired schema. But, the key to this blog post is when you then forget to come back and clean these tables up. We can easily find them with a dbatools command.
Read on to see how you can use dbatools cmdlets to find and remove these deprecated tables.
Comments closedPatrick Gruenauer checks out a new module:
The new PowerShell Entra ID Module is a powerful tool designed to streamline the management and automation of Microsoft Entra resources. This module is part of the Microsoft Graph PowerShell SDK and offers a range of functionalities to enhance administrative tasks.
One of the key benefits of the Microsoft Entra PowerShell module is its focus on usability. Unlike MS Graph, the module is more user-friendly to operate. Let’s dive in.
Click through for a demonstration of what you can do with it.
Comments closedMike Robbins performs some filtering:
PowerShell 3.0 introduced several notable improvements to its cmdlet library, with one of the most useful features in this release being the simplified syntax for the
Where-Objectcmdlet. This enhancement made filtering objects more efficient and user-friendly by introducing Property and Value parameters and a switch parameter for every comparison operator. This article explores how these changes work, their usefulness, and how to leverage them in your scripts.
Read on for a bit of history, as well as the options available to you now.
Comments closedShane O’Neill wants to know what version you’re on:
I will discuss different ways to get the SQL Server version using PowerShell.
I’ll explore a function you can use, and how a dictionary/hash table could also work.
Finally, I’ll discuss how neither of these are needed since dbatools/SMO has a better way.
Click through for scripts and examples.
Comments closedPeter Schott needs to perform a series of bulk inserts:
Sometimes you need to extract a large number of tables into some other format. I’ve written about BCP earlier as a quick option to move data around, but what if you need a delimited text file or a way to repeat the calls without too much trouble across a set of servers? I had this come up recently and wrote up a combination of PowerShell, the sqlserver module, and the bcp.exe tool to allow for a variety of extract types and also, importantly, ensure that we get header data with those extracts in case we need them to pull in to another process. This is doable without the header piece, but many processes (and humans) expect some sort of header data. Thus – a slightly expanded process.
Click through for a script and an explanation of the process.
Comments closed