PowerShell SQL Server Instance Inventory

Purpose

Collects a structured inventory of SQL Server instances across a list of target hosts:

• SQL Server version and edition
• Database count, total size, and recovery models
• Service account and startup type
• Always On AG membership

Output is written to CSV for import into a spreadsheet or monitoring system.

Required Permissions

• SQL Server: VIEW SERVER STATE and VIEW DATABASE STATE
• Windows: local administrator on target hosts (for service account query via WMI)
• Execution policy must allow running scripts: Set-ExecutionPolicy RemoteSigned

Risk Level

Low. Read-only. No changes to SQL Server configuration or data.

Script

#Requires -Version 5.1

<#
.SYNOPSIS
    SQL Server instance inventory collection.
.DESCRIPTION
    Connects to each host in $SqlHosts, collects instance metadata,
    and writes results to CSV.
.NOTES
    Permissions: VIEW SERVER STATE on each SQL instance.
    Windows admin: Required for WMI service account query (optional section).
    Test against non-production instances first.
#>

[CmdletBinding()]
param(
    [string[]]$SqlHosts = @('localhost'),   # Replace with your host list
    [string]$OutputPath = '.\sql_inventory.csv',
    [string]$SqlUser = '',                   # Leave empty for Windows auth
    [string]$SqlPassword = ''
)

$inventory = [System.Collections.Generic.List[PSObject]]::new()

$connectSql = @'
SELECT
    SERVERPROPERTY('ServerName')         AS instance_name,
    SERVERPROPERTY('ProductVersion')     AS version,
    SERVERPROPERTY('ProductLevel')       AS product_level,
    SERVERPROPERTY('Edition')            AS edition,
    SERVERPROPERTY('Collation')          AS collation,
    SERVERPROPERTY('IsClustered')        AS is_clustered,
    SERVERPROPERTY('IsHadrEnabled')      AS hadr_enabled,
    (SELECT COUNT(*) FROM sys.databases WHERE database_id > 4) AS user_db_count;
'@

$dbSql = @'
SELECT
    name,
    state_desc,
    recovery_model_desc,
    CAST(SUM(size) * 8.0 / 1024 AS DECIMAL(10,1)) AS size_mb
FROM sys.databases AS d
JOIN sys.master_files AS mf ON d.database_id = mf.database_id
WHERE d.database_id > 4
GROUP BY name, state_desc, recovery_model_desc
ORDER BY size_mb DESC;
'@

foreach ($host in $SqlHosts) {
    Write-Host "Connecting to $host..."
    try {
        $connStr = if ($SqlUser) {
            "Server=$host;User Id=$SqlUser;Password=$SqlPassword;TrustServerCertificate=True;"
        } else {
            "Server=$host;Integrated Security=True;TrustServerCertificate=True;"
        }

        $conn = New-Object System.Data.SqlClient.SqlConnection($connStr)
        $conn.Open()

        # Instance-level info
        $cmd = $conn.CreateCommand()
        $cmd.CommandText = $connectSql
        $reader = $cmd.ExecuteReader()
        $reader.Read() | Out-Null

        $instanceRow = [PSCustomObject]@{
            Host          = $host
            InstanceName  = $reader['instance_name']
            Version       = $reader['version']
            ProductLevel  = $reader['product_level']
            Edition       = $reader['edition']
            Collation     = $reader['collation']
            IsClustered   = $reader['is_clustered']
            HadrEnabled   = $reader['hadr_enabled']
            UserDbCount   = $reader['user_db_count']
            CollectedAt   = (Get-Date -Format 'yyyy-MM-dd HH:mm:ss')
        }
        $reader.Close()

        $inventory.Add($instanceRow)
        Write-Host "  ✓ $($instanceRow.InstanceName) — $($instanceRow.Version) — $($instanceRow.Edition)"

        $conn.Close()
    }
    catch {
        Write-Warning "Failed to connect to $host`: $_"
        $inventory.Add([PSCustomObject]@{
            Host        = $host
            InstanceName = 'CONNECTION_FAILED'
            Version     = $null
            Edition     = $null
            CollectedAt = (Get-Date -Format 'yyyy-MM-dd HH:mm:ss')
        })
    }
}

$inventory | Export-Csv -Path $OutputPath -NoTypeInformation -Encoding UTF8
Write-Host "`nInventory written to $OutputPath ($($inventory.Count) instances)"

Usage

# Windows auth, single host
.\sql-inventory.ps1 -SqlHosts 'SQLSERVER01'

# Windows auth, multiple hosts
.\sql-inventory.ps1 -SqlHosts 'SQLSERVER01','SQLSERVER02','SQLSERVER03'

# SQL auth
.\sql-inventory.ps1 -SqlHosts 'SQLSERVER01' -SqlUser 'monitor_user' -SqlPassword 'yourpassword'

Expected Output

CSV with columns: Host, InstanceName, Version, ProductLevel, Edition, Collation, IsClustered, HadrEnabled, UserDbCount, CollectedAt.

Hosts that fail to connect appear with InstanceName = CONNECTION_FAILED.

Safe Execution Guidance

• Run from a workstation or jump server with network access to target SQL Server hosts.
• Use a dedicated monitoring account with VIEW SERVER STATE rather than sa or a DBA’s personal credentials.
• Do not embed SQL passwords in scripts stored in version control. Use Windows auth or a secrets manager.

Rollback Steps

Not applicable — read-only script.