Automating SQL Server Health Checks with PowerShell (Invoke-Sqlcmd Walkthrough)

 
If you’re a DBA or sysadmin managing more than one or two SQL Server instances, you already know the drill: log in, run the same handful of queries, eyeball the results, move to the next server. Do that every morning for a few years and it stops being “quick” — it’s just quiet, repetitive risk. One skipped check is usually the one that mattered.

This guide walks through building a simple PowerShell health check script using Invoke-Sqlcmd, from your first connection test to a scheduled task that emails you a report every morning. No prior PowerShell scripting experience required — just basic comfort with SQL Server.

 

What You’ll Need

• PowerShell 5.1 or PowerShell 7+
• The SqlServer PowerShell module (we’ll install it below)
• A Windows or SQL login with VIEW SERVER STATE permission on the instances you’re checking
• Network access to the target SQL Server instance(s) on their listening port (usually 1433)

 

Step 1: Install the SqlServer Module

Invoke-Sqlcmd used to ship with the older SQLPS module, but that’s deprecated. Install the current one from the PowerShell Gallery:

Install-Module -Name SqlServer -Scope CurrentUser -AllowClobber

If you get a permissions error, run PowerShell as Administrator, or add -Scope CurrentUser (shown above) to install it just for your account.

Verify it installed correctly:

Get-Module -Name SqlServer -ListAvailable
 

Step 2: Test Your Connection

Before building anything complex, confirm you can actually reach the server:

Invoke-Sqlcmd -ServerInstance "YourServerName" -Query "SELECT @@SERVERNAME AS ServerName, @@VERSION AS Version"

Replace "YourServerName" with your instance name (use ServerName\InstanceName for named instances). If this returns a result, you’re ready to move on. If it fails, it’s almost always one of three things: firewall blocking port 1433, the account doesn’t have login rights, or you typed the instance name wrong.

 

Step 3: Build the Individual Health Checks

Rather than one giant script, it helps to think of a health check as a set of small, independent queries. Here are six that cover the checks most DBAs run daily.

Check 1: Database Status

Databases that aren’t ONLINE are the first thing worth knowing about.

$query = "SELECT name, state_desc FROM sys.databases WHERE state_desc <> 'ONLINE'"
Invoke-Sqlcmd -ServerInstance $server -Query $query

Check 2: Backup Age

A database with no recent backup is a ticking clock. This checks how many hours since the last full backup:

$query = @"
SELECT
    d.name AS DatabaseName,
    MAX(b.backup_finish_date) AS LastBackup,
    DATEDIFF(HOUR, MAX(b.backup_finish_date), GETDATE()) AS HoursSinceBackup
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b
    ON d.name = b.database_name AND b.type = 'D'
WHERE d.database_id > 4
GROUP BY d.name
ORDER BY HoursSinceBackup DESC
"@
Invoke-Sqlcmd -ServerInstance $server -Query $query

Check 3: Free Disk Space

Low disk space quietly causes more outages than almost anything else. This uses the sys.dm_os_volume_stats DMV, available from SQL Server 2012 onward:

$query = @"
SELECT DISTINCT
    vs.volume_mount_point,
    CAST(vs.available_bytes / 1073741824.0 AS DECIMAL(10,2)) AS FreeSpaceGB,
    CAST(vs.total_bytes / 1073741824.0 AS DECIMAL(10,2)) AS TotalSpaceGB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
"@
Invoke-Sqlcmd -ServerInstance $server -Query $query

Check 4: Blocking Sessions

Long-running blocking chains are one of the more common “why is the app slow” causes:

$query = @"
SELECT
    blocking_session_id AS BlockingSession,
    session_id AS BlockedSession,
    wait_type,
    wait_time / 1000 AS WaitTimeSeconds,
    wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
"@
Invoke-Sqlcmd -ServerInstance $server -Query $query

Check 5: Failed SQL Server Agent Jobs (Last 24 Hours)

$query = @"
SELECT
    j.name AS JobName,
    h.run_date,
    h.run_time,
    h.message
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
WHERE h.run_status = 0
    AND h.run_date >= CONVERT(VARCHAR(8), DATEADD(DAY, -1, GETDATE()), 112)
"@
Invoke-Sqlcmd -ServerInstance $server -Query $query

Check 6: Recent SQL Server Error Log Entries

$query = "EXEC sp_readerrorlog 0, 1, 'Error'"
Invoke-Sqlcmd -ServerInstance $server -Query $query
 

Step 4: Combine Everything Into One Script

Individual checks are useful for testing, but you want one script that runs all of them and gives you a single report. Here’s a complete version that loops through a list of servers and writes the results to CSV files:

# HealthCheck.ps1

$Servers = @("SQLPROD01", "SQLPROD02", "SQLPROD03")
$ReportPath = "C:\HealthChecks\$(Get-Date -Format 'yyyy-MM-dd')"

New-Item -Path $ReportPath -ItemType Directory -Force | Out-Null

$checks = @{
    "DatabaseStatus" = "SELECT name, state_desc FROM sys.databases WHERE state_desc <> 'ONLINE'"
    "BlockingSessions" = "SELECT blocking_session_id, session_id, wait_type, wait_time/1000 AS WaitSeconds FROM sys.dm_exec_requests WHERE blocking_session_id <> 0"
    "FailedJobs" = "SELECT j.name AS JobName, h.run_date, h.message FROM msdb.dbo.sysjobhistory h JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id WHERE h.run_status = 0 AND h.run_date >= CONVERT(VARCHAR(8), DATEADD(DAY, -1, GETDATE()), 112)"
}

foreach ($server in $Servers) {
    Write-Host "Checking $server..."

    if (-not (Test-Connection -ComputerName ($server.Split('\')[0]) -Count 1 -Quiet)) {
        Write-Warning "$server did not respond to ping. Skipping."
        continue
    }

    foreach ($checkName in $checks.Keys) {
        try {
            $result = Invoke-Sqlcmd -ServerInstance $server -Query $checks[$checkName] -QueryTimeout 30 -ErrorAction Stop
            if ($result) {
                $result | Export-Csv -Path "$ReportPath\$server-$checkName.csv" -NoTypeInformation
                Write-Warning "$server : $checkName returned $($result.Count) row(s)"
            }
        }
        catch {
            Write-Warning "$server : $checkName failed - $($_.Exception.Message)"
        }
    }
}

Write-Host "Health check complete. Reports saved to $ReportPath"

This version only writes a CSV when a check actually returns something — a clean run produces no files, which makes it easy to glance at the report folder and know instantly whether anything needs attention.

 

Step 5: Email the Results (Optional but Recommended)

If you’d rather have the report land in your inbox instead of checking a folder, add this after the loop:

$reportFiles = Get-ChildItem -Path $ReportPath -Filter "*.csv"

if ($reportFiles.Count -gt 0) {
    Send-MailMessage `
        -From "dba-alerts@yourcompany.com" `
        -To "you@yourcompany.com" `
        -Subject "SQL Server Health Check - Issues Found - $(Get-Date -Format 'yyyy-MM-dd')" `
        -Body "See attached reports. $($reportFiles.Count) issue file(s) generated." `
        -Attachments $reportFiles.FullName `
        -SmtpServer "smtp.yourcompany.com"
}

Send-MailMessage is marked obsolete in newer PowerShell versions but still works fine in Windows PowerShell 5.1, which is what most scheduled tasks on Windows Server run under. If you’re on PowerShell 7+ and want a future-proof option, look at the MailKit-based approach or a simple SMTP client call instead.

 

Step 6: Schedule It

Option A — Windows Task Scheduler (simplest, works everywhere):

• Open Task Scheduler → Create Task
• Trigger: Daily, set your preferred time (early morning is typical)
• Action: Start a program — Program: powershell.exe, Arguments: -ExecutionPolicy Bypass -File "C:\Scripts\HealthCheck.ps1"
• Run whether user is logged on or not, using a service account with the right SQL permissions

Option B — SQL Server Agent Job (if you’d rather keep it inside SQL Server):

• New Job → New Step → Type: PowerShell
• Command: point it at your script path
• Schedule as usual under the job’s Schedules tab

Either works. Task Scheduler is easier to troubleshoot from a Windows admin’s perspective; a SQL Agent job keeps everything visible to your DBA team inside SSMS.

 

Common Errors and Fixes

“The term ‘Invoke-Sqlcmd’ is not recognized” — the SqlServer module isn’t installed or isn’t imported. Run Import-Module SqlServer at the top of your script to be safe.

“A network-related or instance-specific error occurred” — usually a firewall, wrong instance name, or SQL Server not listening on the expected port. Confirm with Test-NetConnection -ComputerName YourServer -Port 1433.

Login failures under a scheduled task but not when run manually — the scheduled task is running under a different account than the one you tested with. Set the task to run under a service account that has login rights on every target instance.

Script runs but returns nothing, even when you know there’s an issue — check that the account has VIEW SERVER STATE permission; several of these DMVs return empty result sets without it instead of throwing an error.

 

FAQ

What permissions does Invoke-Sqlcmd need for a health check script?

At minimum, the login needs CONNECT rights on the instance and VIEW SERVER STATE permission to read the dynamic management views used in these checks (blocking sessions, disk stats, etc.). It doesn’t need sysadmin.

Can Invoke-Sqlcmd check multiple SQL Server instances in one script?

Yes. Loop through an array of server names and call Invoke-Sqlcmd once per server per check, as shown in the combined script above.

Does this work with Azure SQL Database?

Yes, with adjustments. Some DMVs used here (like backup history from msdb) aren’t available the same way in Azure SQL Database, since Microsoft manages backups directly. Azure SQL Managed Instance is closer to on-premises SQL Server and most of these checks will work as-is.

Is Invoke-Sqlcmd better than using SMO for health checks?

For straightforward T-SQL-based checks like the ones here, Invoke-Sqlcmd is simpler and faster to write. SMO (SQL Server Management Objects) is worth using when you need to manipulate SQL Server objects programmatically, not just query them — it’s a heavier tool for a different job.

How do I run this without hardcoding a password in the script?

Use Windows Authentication (the default when no -Username/-Password is passed) and run the scheduled task under a service account, or store SQL credentials securely using Get-Credential combined with an encrypted credential file rather than plain text in the script.

Have a check you’d add to this list, or run into an error not covered here? Drop a comment below.

 

You may also be interested in reading these articles:

T-SQL Technique: Identifying Top-N Selling Products by Category Using RANK() and CTEs