As a DBA, you are very likely to maintain an up-to-date inventory of all your SQL Servers. It is hard to imagine any environment where this is not the case. However, keeping that inventory current, whether manually or through automation, is another matter. You also need to capture and track detailed metadata about these SQL Servers, such as:
- What edition and version is each SQL Server instance running?
- How are instance‑level settings configured across all servers?
- Which sp_configure options differ from your standard baseline?
You can get these answers from SSMS or by running T‑SQL on each server, but that becomes painful once you manage more than a handful of instances. A simple PowerShell script can collect the same instance‑level metadata into CSV files that you can open in Excel or load into a reporting tool. This though is not meant to replace detailed configuration management or full CMDB tools, but it gives you a solid, scriptable foundation that you can extend over time.
My goal here is to have a reusable script that is easy to understand, easy to run, and easy to adapt as your environment grows.
Why use PowerShell and SMO for metadata?
SQL Server exposes a huge amount of metadata through catalog views, DMVs, and system functions. That’s great when you are writing T‑SQL, but it can be less convenient when you want to:
- Pull the same metadata from many instances.
- Store the results as files for later analysis or auditing.
- Automate the process so it runs on a schedule.
PowerShell gives you a scripting environment that is very good at automation, loops, and working with files. SMO SQL Server Management Objects is a .NET library that exposes SQL Server objects (like servers, databases, tables, and so on) as objects you can work with directly in PowerShell.
To keep the script focused and easy to understand, we will export only instance‑level metadata. That means our script will not touch database‑level or object‑level details like tables or indexes; it stays at the server/instance layer.
The script collects three categories of data:
- Information properties: These come from Server.Information in SMO and include things like version, edition, collation, and operating system information. They give you a high‑level description of the instance.
- Settings: These come from Server.Settings and describe how the instance is configured at a higher level (for example, settings that are not necessarily exposed through sp_configure).
- Configuration (sp_configure‑style): These come from Server.Configuration and map closely to what you see when you run sp_configure. They include both the current run values and the config values that take effect after a restart.
The script also adds a few helpful context columns to every CSV:
- Host name.
- Host IP address (IPv4).
- Number of databases on that instance.
- Server instance name (e.g., ServerA\SQL2019).
This extra information makes it easier to filter and group your data later.
Prerequisites and permissions
Before you run the script, make sure you have:
- PowerShell 5.1 or later, or PowerShell 7.x on your admin workstation or jump server.
- The SqlServer PowerShell module installed. This module includes SMO and is the recommended way to access SQL Server from PowerShell. You can install it with:
- Network connectivity to the SQL Server instance(s) you want to query.
- Enough permissions on SQL Server. In most cases, you’ll want to be a member of the sysadmin role or at least have permissions to view server‑level metadata.
Note that modern SQL Server versions use metadata visibility rules, so if your login does not have the required permissions, some properties may be hidden.
The PowerShell script:
As the script is bit lengthy, you can download it from the GitHub:
SQL Server Metadata Export ScriptYou can save this script as Export-InstanceMetadata.ps1.
This script keeps the logic straightforward:
- It uses parameters so you can pass in the instance name, output folder, and optional SQL credentials.
- It makes sure the output folder exists.
- It loads the SqlServer module (and fails fast with a clear message if it’s missing).
- It builds wide PSCustomObjects where each property corresponds to a metadata field, and each row corresponds to a specific instance.
- It exports three CSV files, one for information, one for settings, and one for configuration values.
Running the script: Basic examples
Once you save the script (for example, as Export-InstanceMetadata.ps1), you can run it in several ways.
Single instance with Windows authentication
If your current Windows account has permissions on the SQL instance, you can simply run:
.\Export-InstanceMetadata.ps1 ` -ServerInstance "SQL01","SQL02\INST1","SQL03" ` -OutputFolder "C:\SQL\Metadata" ` -Verbose
This will: Connect to SQL Server using Windows authentication.
- Write three CSV files into C:\SQL\Metadata:
- SQLInstanceMetadata_Info.csv
- SQLInstanceMetadata_Settings.csv
- SQLInstanceMetadata_Config.csv
- Show progress messages because we used -Verbose.
Using SQL authentication
If you need to use a SQL login instead of Windows auth, you can pass a credential object:
$cred = Get-Credential # enter SQL login name and password .\Export-InstanceMetadata.ps1 ` -ServerInstance "ProdSql01" ` -OutputFolder "C:\SQL\Metadata" ` -SqlCredential $cred ` -Verbose
The script will then connect using that login and password, while the rest of the logic stays the same.
Scaling out: Multiple instances in a loop
The real power of this approach shows up when you have many instances. You can keep a simple text file of instance names and loop through them.
SQLServer1\SQL2019 ProdSql01 ProdSql02 TestSql0
You can run:
$instances = Get-Content "C:\SQL\instances.txt" .\Export-InstanceMetadata.ps1 ` -ServerInstance $instances ` -OutputFolder "C:\SQL\Metadata" ` -Verbose
Working with the CSV output
The script produces three “wide” CSV files per instance. That means:
- Every row represents exactly one instance (or one instance + value type in the config file).
- Every column represents a property (for example, Edition, Version, IsClustered, or max server memory (MB)).
- This layout is especially friendly for tools like:
- Excel or Power BI, where you can quickly filter, sort, and build simple charts.
- A central repository or inventory database, where you might append or merge results from multiple runs to track changes over time.
For example, you can:
- Compare max server memory (MB) across all instances to spot outliers.
- Filter by Version to see which servers are still running out of support version of SQL Server.
- Track VersionString to know which instances need patching.
If you prefer a “long” format (one row per property per instance), you can easily transform the CSV later in PowerShell or another tool, but starting with the wide format makes it easy for junior DBAs to explore the data.
When should you use this approach?
This instance‑metadata export is most useful when you want to:
- Build a quick inventory of instance‑level configuration across many servers.
- Capture a snapshot before and after making changes to compare configuration values.
- Feed metadata into another process or review tool without logging in to SSMS for each server.