Blog Post

PowerShell Strikes Back: A New Script

,

This is it. The final chapter of PowerShell Strikes Back.

Over the past four weeks, you’ve built a real foundation. You know that single quotes are literal and double quotes expand variables. You know how to store and use data in variables – strings, integers, booleans, and hashtables. You know how to loop over a collection and do something useful to each item, including handling errors when a server doesn’t cooperate.

Now we put it all together. No new concepts this week, just everything you’ve learned working as a single, practical script that solves a real DBA problem. Think of it as your Jedi trials. The training is done. Time to use the Force.

The script we’re building: a multi-server SQL Server environment health check that loops over a list of instances, checks SQL Server and SQL Agent service status, queries for databases not in the FULL recovery model, and writes a clean timestamped report to a log file. Something you could actually schedule and use.

The Script: Environment Health Check

Let’s walk through it section by section, then see it all together at the end.

Section 1: Configuration Variables

Everything that might change between environments lives at the top as variables. This is the habit from Week 2, no hardcoded values buried in the middle of the script.

# ============================================================
# PowerShell Strikes Back — Multi-Server Health Check
# GarryBargsley.com | PowerShell Newbie Series | May 2025
# ============================================================

# Configuration — update these for your environment
$servers = @(
    'SQL-PROD-01',
    'SQL-PROD-02',
    'SQL-DR-01'
)

$sqlServiceName   = 'MSSQLSERVER'     # Use MSSQL$INSTANCENAME for named instances
$agentServiceName = 'SQLSERVERAGENT'  # Use SQLAgent$INSTANCENAME for named instances
$logPath          = 'C:DBAScriptsLogs'
$logFile          = "$logPathHealthCheck_$(Get-Date -Format 'yyyyMMdd_HHmmss').log"
$expectedRecovery = 'FULL'            # Flag databases not in this recovery model

Notice $logFile uses double quotes so PowerShell expands $logPath and the Get-Date subexpression to build a timestamped filename automatically. Week 1 and Week 2 in action on line one.

Section 2: Helper Function for Writing Output

Instead of calling Write-Host and writing to the log file separately every time, we build one small function that does both at once. Write it once, use it everywhere.

# Helper function — write to console and log file simultaneously
function Write-Log {
    param([string]$Message)
    $timestamp = Get-Date -Format 'yyyy-MM-dd HH:mm:ss'
    $entry = "[$timestamp] $Message"
    Write-Host $entry
    Add-Content -Path $logFile -Value $entry
}

# Create the log directory if it doesn't exist
if (-not (Test-Path $logPath)) {
    New-Item -ItemType Directory -Path $logPath | Out-Null
}

Write-Log "============================================================"
Write-Log "SQL Server Environment Health Check — Starting"
Write-Log "Servers to check: $($servers.Count)"
Write-Log "============================================================"

Section 3: The Main Loop

Here’s where Week 3 takes over. We loop over every server in $servers, check services, run a SQL query, and evaluate the results. The try/catch keeps a single bad server from stopping the whole check.

foreach ($server in $servers) {
    Write-Log ""
    Write-Log "--- Checking: $server ---"

    try {
        # ---- Service Status ----
        $sqlSvc   = Get-Service -Name $sqlServiceName   -ComputerName $server -ErrorAction Stop
        $agentSvc = Get-Service -Name $agentServiceName -ComputerName $server -ErrorAction Stop

        Write-Log "SQL Server Service : $($sqlSvc.Status)"
        Write-Log "SQL Agent Service  : $($agentSvc.Status)"

        # Only run SQL checks if the service is actually running
        if ($sqlSvc.Status -eq 'Running') {

            # ---- Recovery Model Check ----
            $databases = Invoke-Sqlcmd -ServerInstance $server -Query @"
                SELECT name, recovery_model_desc
                FROM sys.databases
                WHERE database_id > 4
                ORDER BY name
"@ -ErrorAction Stop

            Write-Log "User databases found: $($databases.Count)"

            foreach ($db in $databases) {
                if ($db.recovery_model_desc -ne $expectedRecovery) {
                    Write-Log "  WARNING: $($db.name) is in $($db.recovery_model_desc) recovery — expected $expectedRecovery"
                } else {
                    Write-Log "  OK: $($db.name) ($($db.recovery_model_desc))"
                }
            }

        } else {
            Write-Log "  SKIPPING SQL checks — SQL Server service is not Running"
        }

    }
    catch {
        Write-Log "  ERROR on $server : $($_.Exception.Message)"
    }
}

Write-Log ""
Write-Log "============================================================"
Write-Log "Health Check Complete. Log saved to: $logFile"
Write-Log "============================================================"

The Complete Script

Here it is in one block, ready to copy into VS Code or PowerShell ISE:

# ============================================================
# PowerShell Strikes Back — Multi-Server Health Check
# GarryBargsley.com | PowerShell Newbie Series | May 2026
# ============================================================

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

$sqlServiceName   = 'MSSQLSERVER'
$agentServiceName = 'SQLSERVERAGENT'
$logPath          = 'C:DBAScriptsLogs'
$logFile          = "$logPathHealthCheck_$(Get-Date -Format 'yyyyMMdd_HHmmss').log"
$expectedRecovery = 'FULL'

# Helper function
function Write-Log {
    param([string]$Message)
    $timestamp = Get-Date -Format 'yyyy-MM-dd HH:mm:ss'
    $entry = "[$timestamp] $Message"
    Write-Host $entry
    Add-Content -Path $logFile -Value $entry
}

# Create log directory if needed
if (-not (Test-Path $logPath)) {
    New-Item -ItemType Directory -Path $logPath | Out-Null
}

Write-Log "============================================================"
Write-Log "SQL Server Environment Health Check — Starting"
Write-Log "Servers to check: $($servers.Count)"
Write-Log "============================================================"

foreach ($server in $servers) {
    Write-Log ""
    Write-Log "--- Checking: $server ---"

    try {
        $sqlSvc   = Get-Service -Name $sqlServiceName   -ComputerName $server -ErrorAction Stop
        $agentSvc = Get-Service -Name $agentServiceName -ComputerName $server -ErrorAction Stop

        Write-Log "SQL Server Service : $($sqlSvc.Status)"
        Write-Log "SQL Agent Service  : $($agentSvc.Status)"

        if ($sqlSvc.Status -eq 'Running') {

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

            Write-Log "User databases found: $($databases.Count)"

            foreach ($db in $databases) {
                if ($db.recovery_model_desc -ne $expectedRecovery) {
                    Write-Log "  WARNING: $($db.name) is in $($db.recovery_model_desc) recovery — expected $expectedRecovery"
                } else {
                    Write-Log "  OK: $($db.name) ($($db.recovery_model_desc))"
                }
            }

        } else {
            Write-Log "  SKIPPING SQL checks — SQL Server service is not Running"
        }
    }
    catch {
        Write-Log "  ERROR on $server : $($_.Exception.Message)"
    }
}

Write-Log ""
Write-Log "============================================================"
Write-Log "Health Check Complete. Log saved to: $logFile"
Write-Log "============================================================"

Sample Output

[2025-05-26 09:00:01] ============================================================
[2025-05-26 09:00:01] SQL Server Environment Health Check — Starting
[2025-05-26 09:00:01] Servers to check: 3
[2025-05-26 09:00:01] ============================================================
[2025-05-26 09:00:01]
[2025-05-26 09:00:02] --- Checking: SQL-PROD-01 ---
[2025-05-26 09:00:02] SQL Server Service : Running
[2025-05-26 09:00:02] SQL Agent Service  : Running
[2025-05-26 09:00:03] User databases found: 2
[2025-05-26 09:00:03]   OK: AdventureWorks (FULL)
[2025-05-26 09:00:03]   WARNING: HRSystem is in SIMPLE recovery — expected FULL
[2025-05-26 09:00:04]
[2025-05-26 09:00:04] --- Checking: SQL-PROD-02 ---
[2025-05-26 09:00:04] SQL Server Service : Running
[2025-05-26 09:00:04] SQL Agent Service  : Stopped
[2025-05-26 09:00:05] User databases found: 1
[2025-05-26 09:00:05]   OK: OrdersDB (FULL)
[2025-05-26 09:00:06]
[2025-05-26 09:00:06] --- Checking: SQL-DR-01 ---
[2025-05-26 09:00:07]   ERROR on SQL-DR-01 : Cannot open service control manager on computer 'SQL-DR-01'
[2025-05-26 09:00:07]
[2025-05-26 09:00:07] ============================================================
[2025-05-26 09:00:07] Health Check Complete. Log saved to: C:DBAScriptsLogsHealthCheck_20250526_090001.log
[2025-05-26 09:00:07] ============================================================

Three servers checked. A warning surfaced on HRSystem. SQL Agent is stopped on PROD-02, worth investigating. DR-01 was unreachable, but the script kept going and reported it cleanly. Everything is logged to a timestamped file ready to review or email.

Where to Take This Next

This script is a foundation, not a ceiling. Here are a few natural next steps if you want to keep building on it:

  • Add it to a SQL Agent job – run it on a schedule using sqlcmd or a CmdExec step calling PowerShell
  • Add an email alert – use Send-MailMessage or PowerShell’s Send-MgMail to email the log when warnings are found
  • Pull the server list from SQL – instead of a hardcoded array, query a central management server or a config table for the list of instances
  • Add more checks – last backup date, disk space, AG health, job failure counts. The loop structure supports it all

The Series Is Complete – But the Journey Isn’t

Four weeks. Four fundamentals. One complete script. You came in as someone who had dabbled in PowerShell but never really owned it. You’re leaving with quotes, variables, loops, error handling, functions, logging, and a working multi-server health check that you built up from scratch.

The Emperor of manual, repetitive DBA work has been defeated. The rebellion wins.

If you’ve been following along, drop a comment below and let me know if you get the script running in your environment. Did you extend it with additional checks? I’d genuinely love to hear what you built.

And if you missed any weeks, the full series is below. Start at Week 1 and work through in order; each post builds on the last.

Until next time. May the Force be with your scripts.

PowerShell Strikes Back — The Complete Series:

Week 1: Single vs Double Quotes (May 4)

Week 2: Variables (May 11)

Week 3: For Each Loop (May 18)

Week 4: Putting It All Together (May 26) — You are here

The post PowerShell Strikes Back: A New Script appeared first on GarryBargsley.com.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating