SQL Server instance metadata inventory with PowerShell and SMO

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

Track SQL Server Configuration Changes Using the Error Log

If you work with SQL Server long enough, you or someone will eventually want to know, “Did anyone change that setting?” And if you cannot prove it one way or the other, well you might just be in luck. 

The good news is that SQL Server already tracks configuration changes in a few places. You just need to know where to look and what each option can (and cannot) do for you. 

The easiest option: standard reports

The simplest place to start is the SSMS GUI. Right‑click the server → Reports → Standard Reports, and look for the one that shows configuration history.

This standard report reads from SQL Server’s default trace, which being the "default" setting, would have been already running. That means you don't need to setup and enable it. It is just there ready to view, unless of course it was purposely disabled/stopped.

However, the default trace comes with an important limitation: it is small by design. It is typically about 20 MB total, spread across up to 5 rollover files. You cannot change that size, so older entries will eventually roll off as new activity fills the trace.

If you want your own custom tracking, Extended Events is usually a better choice than building a new trace. Microsoft has largely moved the ecosystem toward Extended Events, and that’s where you should focus any new work.

When you need better control: Use Auditing and Extended Events features

SQL Server Auditing and Extended Events can also capture configuration changes, but there is one big catch: you must set them up before the change happens.

If you only think about tracking after the fact, auditing and Extended Events won’t help you. Once a setting is changed, they cannot retroactively tell you when it happened (unless they were already recording).

That is why many DBAs still start with the default trace or the SQL Server error log. Those options are already there, and they can answer the question right now, even if you did not plan for it.

The SQL Server error log

Another powerful, often overlooked place to check is the SQL Server error log. Every time a configuration option is changed with sp_configure, SQL Server usually writes a message into the error log that includes the phrase “Configuration option”.

Because of that, the error log becomes a built‑in change history for key settings, as long as the log files are still present. You can search this log in several ways:

• In SSMS, by opening the SQL Server logs.

• In a text editor, by opening the .log files directly.

• Programmatically, using sys.xp_readerrorlog from T‑SQL.

The sys.xp_readerrorlog procedure is especially handy because it lets you search and filter the log without leaving SQL Server. Microsoft documents it here: sp-readerrorlog

Please note: You may notice that Microsoft documents sp_readerrorlog as the supported way to read SQL Server error logs, while this post uses sys.xp_readerrorlog instead. In practice, both procedures read the same underlying log files and return the same core information; the main difference is that sp_readerrorlog is a wrapper that first checks your permissions and then calls xp_readerrorlog under the hood. The supported version also accepts a slightly simpler parameter list, while xp_readerrorlog exposes extra parameters such as a start date, end date, and sort order, which makes it more flexible for ad‑hoc troubleshooting and filtered searches. Because of this extra flexibility, many DBAs, including examples in this blog post, continue to rely on xp_readerrorlog in day‑to‑day work, even though it remains an undocumented extended stored procedure.

---

Examples: 

Here is a basic example that searches the current SQL Server error log for configuration change:

USE master;
GO
EXEC sys.xp_readerrorlog
    0,          -- LogNumber: 0 = current error log
    1,          -- LogType: 1 = SQL Server error log
    N'Configuration option', -- SearchString1: text to search for
    NULL,       -- SearchString2: additional text filter (none)
    NULL,       -- StartTime: no start‑time filter
    NULL,       -- EndTime: no end‑time filter
    N'desc';    -- Sort order: newest entries first

This returns all entries in the current log file that contain Configuration option, ordered newest first.

If you want to narrow it by time, you can pass a start‑time filter:

USE master;
GO
EXEC sys.xp_readerrorlog
    0,          -- LogNumber: 0 = current error log
    1,          -- LogType: 1 = SQL Server error log
    N'Configuration option', -- SearchString1: text to search for
    NULL,       -- SearchString2: additional text filter (none)
    '2026-05-10 16:00:00', -- StartTime: start datetime filter
    NULL,       -- EndTime: no end‑time filter
    N'desc';    -- Sort order: newest entries first

Now you only see configuration‑change lines from that specific point onward.

You can also search for a specific setting by adding a second text filter. For example, this looks for configuration messages that also contain the word Optimize:

USE master;
GO

EXEC sys.xp_readerrorlog
    0,          -- LogNumber: 0 = current error log
    1,          -- LogType: 1 = SQL Server error log
    N'Configuration option', -- SearchString1: primary text to search for
    N'Optimize', -- SearchString2: secondary text to search for
    NULL,       -- StartTime: no start‑time filter
    NULL,       -- EndTime: no end‑time filter
    N'desc';    -- Sort order: newest entries first

These patterns make it easy to quickly corner the exact change you are investigating.

What happens after a restart

Here is an important detail that trips people up: every time SQL Server restarts, it starts a new error log file. The previous file is renamed to errorlog.1, the one before that becomes errorlog.2, and so on.

By default, SQL Server keeps up to 7 of these archived log files, but you can configure it to keep more (up to 99). If needed, you can also manually rotate the log without restarting by running sp_cycle_errorlog. This is useful if you want to “start fresh” and still keep older logs around.

Because of this cycling behavior, older configuration changes may move into errorlog.1, errorlog.2, and so on. The good news is that sys.xp_readerrorlog can read those archived logs too. The bad news is that the oldest logs will eventually be removed unless you copy that data somewhere else.

So while the error log is a great tool for recent history, it is still not a permanent, long‑term auditing solution.

Usefulness beyond configuration changes

sys.xp_readerrorlog is not only useful for tracking configuration changes. You can also use it to search for errors, warnings, startup messages, and other events.

For example, say you want to check for severity 16 errors containing a specific error number over the last 24 hours:

USE master;
GO

DECLARE @start_time datetime = GETDATE() - 1;

EXEC sys.xp_readerrorlog
    0,          -- LogNumber: 0 = current error log
    1,          -- LogType: 1 = SQL Server error log
    N'Severity: 16', -- SearchString1: primary text to search for
    N'41145',  -- SearchString2: secondary text to search for
    @start_time, -- StartTime: start datetime filter (yesterday)
    NULL,       -- EndTime: no end‑time filter
    N'desc';    -- Sort order: newest entries first

This kind of pattern is very handy when troubleshooting a server and you want to quickly focus on a specific kind of error.

---

The bottom Line:

Here is how I usually think about this in practice:

Need to do a quick check?    Use the Standard Reports in SSMS. They read from the default trace and give you an easy, visual way to see recent changes.

Need more detail, but don’t want to set up a new system?  Use sys.xp_readerrorlog against the SQL Server error log. It is fast, already available, and works great for recent configuration changes.

For long‑term auditing and compliance: Use the Extended Events or SQL Server Auditing. These tools require planning and setup, but they are the right choice if you need reliable, long‑lasting change history.

So if someone tells you that a setting was never touched, check the default trace first. If that has already rolled off, search the error log. Only then, if you need a permanent answer, invest in a more structured auditing solution.