The purpose of this article is to introduce a PowerShell script that I wrote and published on GitHub. The script is a quick and practical way to grab instance‑level configuration from a bunch of servers and dump it into CSV files you can actually use. Instead of clicking through SSMS on every box, you run one PowerShell script, let SMO do the heavy lifting, and end up with three inventory files you can filter, pivot, or feed into whatever reporting you already have.
The approach builds on the ideas in a longer and older post,
SQL Server Metadata using Powershell
,but this version is aimed at people who just want a reliable tool they can run on a schedule and hand to the rest of the team
Scope
The scope of the PowerShell script in the GitHub repo
SQL Instance Metadata Export Script
is intentionally narrow: it focuses on and gathers only instance‑level metadata, and not database or objects level metadata. The goal is a simple inventory you can trust, not a full CMDB.
Output Files
The script writes three CSV files, each one covering a different slice of instance‑level metadata across all the servers you pass in.
Info CSV: Contains attributes from Server.Information (SMO), including version, edition, collation, operating system details, and other identifying properties for each instance.
Settings CSV: Contains attributes from Server.Settings (SMO), including instance-level configuration values that would otherwise need to be obtained from SSMS or dynamic management views (DMVs).
Config CSV: Contains attributes from Server.Configuration (SMO), providing a view of sp_configure style options, including both ConfigValue and RunValue. The script outputs two rows per instance, identified by ValueType = ConfigValue or RunValue, to distinguish persisted values from those currently in effect..
All three files also include:
- ServerInstance
- HostName
- IPAddress
- NumberOfDatabases
That extra context is there so that when you’re doing baselines, you don’t have to join back to another inventory just to figure out which host you’re looking at.
How the script behaves
Under the covers, the script does roughly this:
- Loads the SqlServer PowerShell module so SMO is available.
- Loops through every value you pass into -ServerInstance (or via the pipeline).
- For each instance:
- Connects using Windows auth by default, or SQL auth if you pass -SqlCredential.
- Pulls Server.Information, Server.Settings, and Server.Configuration.
- Builds a wide PSCustomObject for each category using ordered hashtables, so the CSV columns are stable.
- Collects all rows in memory and writes three CSVs once at the end.
Usage
Here are the core patterns you’re likely to use day‑to‑day.
Single instance, default file names
.\Export-InstanceMetadata.ps1 -ServerInstance "ProdSql01"
This creates 3 csv files into your current folder:
- SQLInstanceMetadata_Info.csv
- SQLInstanceMetadata_Settings.csv
- SQLInstanceMetadata_Config.csv
Multiple instances, specific output folder
.\Export-InstanceMetadata.ps1 ` -ServerInstance "SQL01","SQL02\INST1","SQL03" ` -OutputFolder "C:\SQL\Metadata" ` -Verbose
Same three files, but every instance you passed in is represented. -Verbose gives you enough noise to see which instances connected cleanly and which ones failed without drowning you in debug.
Custom prefix for per‑environment baselines
.\Export-InstanceMetadata.ps1 ` -ServerInstance "ProdSql01","ProdSql02" ` -OutputFolder "C:\SQL\Inventory" ` -FileBaseName "SQLProd"
Now your files are:
- SQLProd_Info.csv
- SQLProd_Settings.csv
- SQLProd_Config.csv
This makes it easy to keep separate snapshots for Prod, Test, DR, etc. without renaming files after the fact.
Using SQL authentication
$cred = Get-Credential # SQL login .\Export-InstanceMetadata.ps1 ` -ServerInstance "VendorHostedSql" ` -SqlCredential $cred ` -OutputFolder "C:\SQL\Metadata"
Same behavior, but you’re not relying on domain auth. Handy for vendor‑hosted or isolated environments where Windows auth isn’t an option.
Feeding instance names from a file
Get-Content .\instances.txt | .\Export-InstanceMetadata.ps1 ` -OutputFolder "C:\SQL\Metadata" ` -FileBaseName "SQLInventory"
If you already manage your SQL Servers list somewhere (CMS, text file, whatever), this lets you reuse that list instead of editing the script every time.
Why it’s useful for senior DBAs
This script is mainly about giving you cheap, repeatable visibility:
- Baselines: Capture a snapshot before and after a change window and stick the CSVs in source control or with the CAB artefacts.
- Drift: Load the Config CSV into Power BI or Excel and you can spot weird max server memory, max degree of parallelism, or other oddball settings across the organization in a few clicks.
- Inventory Questions: “How many instances are still on 2016?” “Where are all the Developer Edition installs?” These become filters instead of ad‑hoc T‑SQL in random RDP sessions.
Import CSVs into one Excel workbook with separate tabs
There is no official Microsoft PowerShell module for Excel files, except for using the Excel COM Object. Therefore, I have used CSV files as the output format so far.
However, if you prefer, you can use PowerShell code to combine the 3 separate CSVs into a single Microsoft Excel workbook with tabs. This approach uses a PowerShell module called ImportExcel, which is specifically designed for working with Excel files. Although it is a third-party module and not an official Microsoft product, it is widely adopted and recommended by Microsoft for PowerShell automation.
Install-Module ImportExcel -Scope CurrentUser # Import CSVs into one Excel workbook with separate tabs Import-Csv "C:\Path\SQLInstanceMetadata_Config.csv" | Export-Excel "C:\Path\SQLServerMetaData.xlsx" -WorksheetName "Config" Import-Csv "C:\Path\SQLInstanceMetadata_Info.csv" | Export-Excel "C:\Path\SQLServerMetaData.xlsx" -WorksheetName "Info" Import-Csv "C:\Path\SQLInstanceMetadata_Settings.csv" | Export-Excel "C:\Path\Documents\SQLServerMetaData.xlsx" -WorksheetName "Settings"
See also
- SQL Server Metadata using Powershell
- Powershell script to get list of databases on a server
- Powershell one liner to export data directly from SQL Server to Excel
- GitHub repository script