PowerShell Strikes Back: Return of the Loop

Welcome back to PowerShell Strikes Back. We’re three weeks in, and the training is paying off. In Week 1, we learned that quotes are not interchangeable. In Week 2, we put variables to work – storing server names, config values, service objects, and boolean results. If you’ve been following along and running the examples in your own environment, you’re already writing better PowerShell than you were a month ago.

This week, we tackle the concept that transforms a script from a one-time operation into an actual tool: the ForEach loop.

In Return of the Jedi, the Rebel Alliance didn’t storm one stormtrooper. They faced an entire army, and they dealt with each one systematically. That’s exactly what a ForEach loop does. You have a list. You do something to each item in that list. One by one, until the list is done. For a DBA managing multiple SQL Server instances, this is the difference between running a check once and running it everywhere.

The Concept: Iteration

Iteration means repeating an action for each item in a collection. Instead of writing the same code five times for five servers, you write it once and let the loop handle the repetition. The collection can be anything: an array of server names, a list of databases, a set of files, or query results from SQL Server.

PowerShell gives you a few ways to loop. We’re focusing on the two you’ll use most as a DBA: foreach and ForEach-Object.

The ForEach Statement

The foreach statement is the most readable form and the easiest to reason about when you’re starting. Here’s the basic structure:

foreach ($item in $collection) {
    # Do something with $item
}

Let’s make it real. Here’s a list of SQL Server instances and a loop that checks the SQL Server service status on each one:

$servers = @('SQL-PROD-01', 'SQL-PROD-02', 'SQL-DR-01')

foreach ($server in $servers) {
    $service = Get-Service -Name 'MSSQLSERVER' -ComputerName $server
    Write-Host "$server : $($service.Status)"
}

Output:

SQL-PROD-01 : Running
SQL-PROD-02 : Running
SQL-DR-01 : Running

Three servers checked with four lines of code. Scale that to ten servers, and you don’t change a single line of logic; you add names to the array. That’s the power of iteration.

ForEach-Object: The Pipeline Version

PowerShell has a pipeline, the ability to pass output from one command directly into another using the pipe character |. ForEach-Object is the loop that lives in the pipeline, and inside it, you reference the current item with the automatic variable $_ (or the more readable $PSItem in newer PowerShell versions).

$servers = @('SQL-PROD-01', 'SQL-PROD-02', 'SQL-DR-01')

$servers | ForEach-Object {
    $service = Get-Service -Name 'MSSQLSERVER' -ComputerName $_
    Write-Host "$_ : $($service.Status)"
}

This produces the same output as the foreach version. The pipeline style is common in PowerShell one-liners and scripts that chain multiple commands together. Both approaches are valid; use whichever reads more clearly to you. For multi-line logic inside the loop, most people find foreach more readable. For quick one-liners, ForEach-Object in the pipeline feels more natural.

Looping Over SQL Query Results

One of the most powerful combinations in DBA scripting is Invoke-Sqlcmd feeding results into a foreach loop. This lets you query SQL Server for a list of things and then act on each one.

Here’s a practical example: querying for all user databases on an instance and reporting their recovery model:

$serverName = 'SQL-PROD-01'

$databases = Invoke-Sqlcmd -ServerInstance $serverName -Query @"
    SELECT name, recovery_model_desc
    FROM sys.databases
    WHERE database_id > 4
    ORDER BY name
"@

foreach ($db in $databases) {
    Write-Host "$($db.name) : $($db.recovery_model_desc)"
}

Output:

AdventureWorks : FULL
HRSystem : SIMPLE
OrdersDB : FULL
ReportingDB : SIMPLE

Notice the @" ... "@ syntax that’s a PowerShell here-string, which lets you write a multi-line string cleanly without worrying about escaping quotes. It’s a handy tool when your T-SQL gets longer than a single line.

Now take it a step further. What if you wanted to flag every database not in FULL recovery?

foreach ($db in $databases) {
    if ($db.recovery_model_desc -ne 'FULL') {
        Write-Host "WARNING: $($db.name) is in $($db.recovery_model_desc) recovery model"
    } else {
        Write-Host "$($db.name) : OK"
    }
}

Output:

AdventureWorks : OK
WARNING: HRSystem is in SIMPLE recovery model
OrdersDB : OK
WARNING: ReportingDB is in SIMPLE recovery model

You just built the core logic of an environment audit tool. The loop does the repetition. The if statement does the evaluation. Variables hold the data. Quotes keep everything straight. All four weeks of this series are working together.

Looping Across Multiple Servers and Databases

The real multiplier is nesting a loop inside a loop. The outer loop iterates over servers; the inner loop iterates over databases on each server. Handle with care; nested loops can get complex fast. But for the right problem, they’re exactly the tool:

$servers = @('SQL-PROD-01', 'SQL-PROD-02')

foreach ($server in $servers) {
    Write-Host "`n--- $server ---"

    $databases = Invoke-Sqlcmd -ServerInstance $server -Query @"
        SELECT name FROM sys.databases
        WHERE database_id > 4
        ORDER BY name
"@

    foreach ($db in $databases) {
        Write-Host "  $($db.name)"
    }
}

Output:

--- SQL-PROD-01 ---
  AdventureWorks
  HRSystem

--- SQL-PROD-02 ---
  OrdersDB
  ReportingDB

Two servers, all user databases, are cleanly formatted. That backtick-n (`n) at the start of the Write-Host line is PowerShell’s newline escape character, which adds a blank line before each server header for readability.

Error Handling Inside Loops: Don’t Let One Bad Server Take Down the Whole Mission

In the real world, one server in your list might be unreachable, offline, or throwing an error. Without error handling, PowerShell will crash the loop on that server and never get to the rest. Wrap your loop body in a try/catch to keep the mission moving:

$servers = @('SQL-PROD-01', 'SQL-PROD-02', 'SQL-OFFLINE-03')

foreach ($server in $servers) {
    try {
        $service = Get-Service -Name 'MSSQLSERVER' -ComputerName $server -ErrorAction Stop
        Write-Host "$server : $($service.Status)"
    }
    catch {
        Write-Host "$server : ERROR - $($_.Exception.Message)"
    }
}

Output:

SQL-PROD-01 : Running
SQL-PROD-02 : Running
SQL-OFFLINE-03 : ERROR - Cannot find any service with service name 'MSSQLSERVER'

The loop continues past the failed server, reports the error cleanly, and finishes the job. This is the pattern you want in production scripts. One rogue server should never abort the whole operation, even Obi-Wan kept fighting after losing a hand.

Next Week: The Series Finale

Next Monday, May 25th, we close out PowerShell Strikes Back with the series finale, putting everything together into one real, practical DBA script. Quotes, variables, and loops work as a unit to solve an actual problem you might face in your environment. Think of it as your Jedi trials.

In the meantime, take this week’s loop examples and run them in your lab. Swap in your own server names. Try looping over sys.databases on one of your instances. Make the script your own.

See you on May 25th.

This post is part of the PowerShell Strikes Back series – a four-week May series for the SQL Server DBA. New posts every Monday in May.

Week 1: Single vs Double Quotes | Week 2: Variables

The post PowerShell Strikes Back: Return of the Loop appeared first on GarryBargsley.com.