Joey on SQL Server

DBAs: You Need To Learn PowerShell

It's time. Make your life a lot easier by automating those time-intensive tasks.

• By Joey D'Antoni
• 06/03/2022

Whenever I give a talk on databases or even Azure, I typically ask for a show of hands to see what percentage of the audience has worked with PowerShell. Typically, this is about 20 to 30 percent of my audience. This makes me wonder: why aren't DBAs learning PowerShell?

In the early days of using PowerShell with databases, it was exceptionally painful, to do something simple such as executing a query. Even then, PowerShell had a cult following of DBAs who mostly used it for operating system functions, but made the occasional call to a SQL Server.

I still remember the first time I knew I had to learn PowerShell. I was in my first month of working at Comcast and there was some flaw in Windows or SQL Server that required us to check approximately five thousand servers for either a check box or registry key. While, I could have logged into all five thousand servers and checked off an Excel spreadsheet, that process was prone to error (and made me want to cry). At that point I had to get started learning PowerShell.

In my early days, I referred to The Scripting Guy blog, which was started by a gentleman named Ed Wilson, who worked at Microsoft. Ed has since retired, but the blog is preserved and continued here by new staff.

You might ask, "I only manage a handful of servers. Why do I need to learn PowerShell?" Do you want to implement an easy-to-use set of configuration checks against your database? Or perform a migration of users and SQL Server Agent jobs from one server to another? While I'm highlighting the DBATools and DBA Checks open-source projects here, there are any number of ways you can implement PowerShell in your environment to improve your productivity and consistency.

I'll give you an example of something I recently implemented for a client to walk you though the basics of simple PowerShell program and script. The client wanted a way to restore their production databases to their test and staging environments. In their environment, I implemented Ola Hallengren's maintenance solution (another great open source project) to perform the backups. Typically, you could very easily do this using restore-dbadatabase cmdlet from DBATools. However, in this case, we are backing to Azure storage, which limits the ability of DBATools to look for backup. However, Ola's code optionally logs the commands to backup the database to a table in the master database called CommandLog.

First thing's first. To get this process started, I needed to deploy a stored procedure on the server from which I was getting the commands to the restore the database.

USE [master]

  GO 

CREATE OR ALTER PROCEDURE [dbo].[getLastBackup]  @db NVARCHAR(256)

  AS 

  SET NOCOUNT ON 
SELECT D.Command

  FROM sys.databases AS S

  CROSS APPLY (

  SELECT TOP 1 Command

  FROM dbo.commandLog AS CL

  WHERE CL.DatabaseName = S.name

  AND StartTime > DATEADD(DAY, - 14, getdate())

  AND CommandType = 'BACKUP_DATABASE' 

  AND Command LIKE '%URL%' 

  AND Command NOT LIKE '%DIFFERENTIAL%' 

  ORDER BY CL.StartTime DESC 

  ) AS D

  WHERE S.name = @DB;

This procedure accepts an input parameter of database name and outputs the commands used to last do a full backup of that database. (Optionally, you could just do a restore of all the databases by dropping that parameter.) You'll note that I exclude differential and log backups. You could add them, but for the sake of brevity, I'm excluding them from this example. You may ask, "I thought this was about PowerShell. Why are you showing me T-SQL?" Simply speaking, while I could have executed this in PowerShell (maybe) it took me about 10 minutes to write in T-SQL. Use the right tool for the job. Let's dig into the PowerShell.

Param ([string] $db)

$ErrorActionPreference = "Stop" 
sqlcmd -E -S prodserver -Q "exec  master.dbo.getlastbackup @db=$db"  -y2000 -h-1 -o  C:\scripts\restorescript$db.sql /v db="$db" 
(get-content C:\scripts\restorescript$db.sql).Replace('BACKUP DATABASE','RESTORE DATABASE')|set-content  C:\scripts\restorescript$db.sql

  (get-content C:\scripts\restorescript$db.sql).Replace('TO URL','FROM URL')|set-content  C:\scripts\restorescript$db.sql
$w = Get-Content C:\scripts\restorescript$db.sql

  $w -replace "( with  ).*$","  WITH"|set-content C:\scripts\restorescript$db.sql
(get-content C:\scripts\restorescript$db.sql).Replace('WITH','WITH REPLACE,  STATS=5;')|set-content C:\scripts\restorescript$db.sql
sqlcmd -S stageserver -E -i C:\scripts\restorescript$db.sql

At the beginning of this script I'm defining a parameter with a data type of stringcalled $DB. This will be the name of the database being restored, which will be passed to the stored procedure. When this is called, it will be from a SQL Agent job step, as shown below, and the parameter is passed in.

I set the error action preference to stop. I would like the job to fail if there are any PowerShell errors. Then I can run the executable sqlcmd (you could use invoke-sqlcmd or invoke-dbaquery here, but I'm choosing to use sqlcmd because it's easy and works) with the flags -E and -S (Windows auth and servername respectively), -Q which executes a query and then exits and, most important, -y 2000 and -h-1 (which ensure that my output file which is reference by the -o flag is clean and doesn't have any data getting truncated). Finally, the "/v db=" accepts the parameter from the calling job. This is a start. However, it's a list of BACKUP commands and we need to restore. How do I fix that? PowerShell to the rescue.

My first line of PowerShell simply called the get-content cmdlet on my newly created file and used the .replace method to replace BACKUP to RESTORE. I do the same thing again on the next to replace TO URL to FROM URL. In both cases I'm using a pipe ("|") to send the output of the first cmdlet to second cmdlet, called set-content, which effective saves the content changes my code mad. It gets a little bit trickier from here.

I needed to remove everything that was after the word WITH in the backup statement. Once again I'm doing a get-content. But this time I'm assigning to a variable called $w. Because the output is an object, I can use the -replace switch to remove everything after WITH and once again write the content. Finally, I do a simple REPLACE of WITH, with WITH REPLACE, STATS=5 so I can overwrite the existing database on the target environment.

The final step is to call sqlcmd once again, to execute the restore script created by this process. I am not a wizard at PowerShell, and someone who was really good could probably do this in two lines of code. However, it takes a little bit of a complex process, and automates it reliably and consistently.

PowerShell was started at Microsoft to supply a more usable scripting interface to Windows. Jeffery Snover, who tasked with starting the process, built a document called the Monad (codename for PowerShell) manifesto with the fundamental tenants of the language that lays out the framework for the language. PowerShell combines the notion of traditional Linux shells with robust .NET methods to allow users to create powerful scripts very quickly.

While the script you looked at here is basic, you could easily extend to run across your enterprise just by using some of the basic looping abilities within PowerShell. Finally, for DBAs, if you are looking for a resource to help with learning PowerShell, some good friends of mine have written Learn dbatools in a Month of Lunches.