Press "Enter" to skip to content

Category: Powershell

Test those Backups

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.

Leave a Comment

Parsing SQL Server Query Stats in Powershell

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.

Leave a Comment

Parallelism in Powershell Workflows

Chad Callihan shows one method for parallel execution in Powershell:

I’ve mentioned before that running Copy-DbaDatabase worked well in my experience, but the downside that I ran into was utilizing it for larger counts of databases. It should be no surprise that the more databases to copy, the longer the process takes to complete. I did some more research and came across using workflows along with the parallel keyword to speed up the copy process.

Let’s take a look at what a workflow is and how we can apply it to add parallelism to the database copy process.

I’ve previously covered options for parallelism, though apparently I was wrong about workflows being deprecated. Rod Edwards also has a list of options for doing things in parallel and does not include workflows, so you can get an idea of several of the options available to you.

Comments closed

Finding Azure Region Pairs via Powershell

Mike Robbins is looking for a match:

In this article, you learn how to add a custom property to the Azure PowerShell Get-AzLocation cmdlet output to display the physical location for paired regions in Azure. This information isn’t available by default but can help you understand the resiliency and redundancy of your Azure resources like geo-redundant storage (GRS) and other Azure services that rely on Azure Storage for replication.

Read on to see how to do this.

Comments closed

PowerShell Script to Move Azure SQL DB from General Purpose to Business Critical

Sakshi Gupta shares a script:

Recently, we faced a requirement to upgrade large number of Azure SQL databases from general-purpose to business-critical.

As you’re aware, this scaling-up operation can be executed via PowerShell, CLI, or the Azure portal and follow the guidance mentioned here – Failover groups overview & best practices – Azure SQL Managed Instance | Microsoft Learn

Given the need to perform this task across a large number of databases, individually running commands for each server is not practical. Hence, I have created a PowerShell script to facilitate such extensive migrations.

Click through for the scenarios Sakshi tested, some important considerations, and the script itself.

Comments closed

Thoughts on Start-DbaMigration in dbatools

Chad Callihan shares an experience:

It’s time to follow up on a post from a few months ago where I said I’d give my thoughts on Start-DbaMigration from dbatools after picking up more experience. Along with Start-DbaMigration I picked up some experience with Copy-DbaDatabase to migrate databases from one server to another. Below are some thoughts on the good and bad.

Read for Chad’s scenario and some of the good and the bad of Start-DbaMigration.

Comments closed

Choosing between Add-Type and New-Object

Patrick Gruenauer contrasts two options in Powershell:

Predefined .NET classes: PowerShell makes certain predefined .NET classes directly available without you having to load them with “Add-Type”.

You can simply use “New-Object, to create instances of these classes. This includes many commonly used classes such as “System.String”, “System.DateTime”, “System.IO.FileInfo”

Read on for a few examples of this, as well as when you would want to use Add-Type instead.

Comments closed

Comparing Configuration of Two SQL Server Instances

Jana Sattainathan checks the labels on these bottles:

A lot of times, you have nearly identical database servers for an application running in Production, Test and Development but you may notice performance differences between them for the same data/queries that you could not attribute to any reason since CPU, Memory, Disk etc., may all be identical.

This is, strictly speaking, a comparison of configurations rather than data differences, indexing, and the like. Nonetheless, it’s useful to make this sort of comparison just to ensure that your instances have your desired state configuration.

Comments closed

Using Powershell to Browse Azure Capabilities

Kay Sauter does some digging:

In a nutshell, Azure follows a structure called the Azure Resource Manager (ARM). Unfortunately, ARM is not human-readable, so you need to a tool to be efficient in using it. There are quite some tools available, and from Microsoft, there is Azure Bicep, Azure CLI and Azure PowerShell. Since I have done some DBA stuff in the past, and I am a fan of dbatools (created by the team here), I find PowerShell the most convenient tool to use and suspect that many will see it the same way if they are using dbatools.

Click through for the post. As of right now, the images are broken, but if you open them in new tabs and remove the extraneous “browsing-azure-with-powershell/” in the URL, you’ll be able to see those images.

Comments closed