SQL Server inventory with dbatools
Which instances do we have, on which version and which patch level? Which databases live on them, who owns them, and how are the servers configured? These questions come up before every audit, every migration and every patch day. In SSMS you answer them one instance at a time.
dbatools answers them for a whole list of instances at once. Get-DbaBuild and Test-DbaBuild assess the patch level, Get-DbaDatabase lists the databases, and Export-DbaInstance writes the configuration out as scripts. This page shows which calls worked in the lab, why the patch assessment is only as current as a reference file, and where the export contains less than you would expect. Tested with dbatools 2.9.1 on Nov 18, 2026; how, is described at the end under "What we measured and what we did not".
In 60 seconds
- The list comes first. A maintained instance list, in a Central Management Server or in a file, is the foundation.
Find-DbaInstanceis a network scanner, not an inventory. Use it only on your own network and with permission. - Assess patch levels with
Test-DbaBuild, not withGet-DbaBuildalone.Get-DbaBuildshowed no warning for SQL Server 2022 RTM, even though 28 CUs were missing. OnlyTest-DbaBuild -MaxBehindshowed the gap. - The assessment is only as current as the build reference. Version 2.9.1 ships a reference dated Oct 13, 2026. With it, 2022 CU27 counted as current. After
Update-DbaBuildReference(as of Nov 12, 2026) it was one CU behind. Include the reference date in your report. Get-DbaBuilddoes not recognize builds newer than its reference. SQL Server 2025 CU10 showed up as CU9 with the old reference, without a warning.- Call
Export-DbaInstancewith-ExcludePassword. The default call needs the Dedicated Admin Connection (DAC). Even locally on Windows, it produced an incomplete export with a secret in plain text. When the DAC was in use, not a single file was written. So check for files, not for error messages. - Databases across all instances into one CSV. One call to
Get-DbaDatabasereturned recovery model, owner, compatibility level, size and last backups. Missing backups appear as01/01/0001 00:00:00, not as an empty field.
1 · The instance list
Almost every dbatools command takes a list of instances with -SqlInstance. Your inventory is therefore only as complete as that list. Two approaches are common:
- Central Management Server or Registered Servers. Many teams already keep their servers in a Central Management Server (CMS).
Get-DbaRegServerreads these entries, so the same list applies in SSMS and in scripts. We did not set up a CMS in the lab; we only describe the command here. - A text file. One instance name per line, named instances as
Computer\Instance. That is how we worked in the lab.
$instances = Get-Content .\instances.txt # one instance name per line, named instances as Computer\InstanceThe file in the Linux lab contained five names:
sql2017
sql2019
sql2022
sql2025
sql2025cu10On the Windows server with two instances, we passed the names directly: SQLSRV-01, SQLSRV-01\SQL2.
Find-DbaInstance searches the network for instances. That helps you find gaps in the list, but it does not replace it (section 6).
2 · Version and patch level
What Get-DbaBuild reports
Get-DbaBuild reads the build number of each instance and looks it up in a reference file. It returns the major version, service pack, CU, KB article and end of support. Against the Windows server with Windows authentication:
# Lab shortcut for self-signed certificates, this session only (part 1)
Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true
Get-DbaBuild -SqlInstance SQLSRV-01, SQLSRV-01\SQL2 |
Format-List SqlInstance, Build, NameLevel, SPLevel, CULevel, KBLevel, BuildLevel, SupportedUntil, MatchType, WarningSqlInstance : SQLSRV-01
Build : 16.0.1000
NameLevel : 2022
SPLevel : RTM
CULevel :
KBLevel :
BuildLevel : 16.0.1000
SupportedUntil : 11.01.2033 00:00:00
MatchType : Exact
Warning :
…Both instances ran SQL Server 2022 RTM, without a single CU. Still, Warning is empty and MatchType is Exact. SupportedUntil applies to the major version, not to the patch level: 2022 CU27 got the same date in the Linux lab. So Get-DbaBuild describes what is installed. It does not tell you whether that is good enough.
Test-DbaBuild shows the gap
Test-DbaBuild compares the same build with a target and sets Compliant:
Test-DbaBuild -SqlInstance SQLSRV-01, SQLSRV-01\SQL2 -MaxBehind 0CU |
Format-Table SqlInstance, Build, BuildTarget, Compliant, CUTarget, MaxBehindResult with the updated build reference (section 3):
SqlInstance Build BuildTarget Compliant CUTarget MaxBehind
----------- ----- ----------- --------- -------- ---------
SQLSRV-01 16.0.1000 16.0.4325 False CU28 0CU
SQLSRV-01\SQL2 16.0.1000 16.0.4325 False CU28 0CUEven with -MaxBehind 30CU, RTM stayed False, with CU1 as the target. A report across many instances therefore needs Test-DbaBuild, not just Get-DbaBuild.
The targets measure different things:
-MaxBehindcounts only CUs (and service packs). Retracted CUs do not count (source code 2.9.1,Test-DbaBuildline 339).-Latestcompares with the newest entry of the major version, security updates included (lines 325–326). In the Linux lab,-Latestwas thereforeFalseon every instance, even where-MaxBehind 0CUreportedTrue: SQL Server 2017 CU31 and 2019 CU32 had the September security update, and the reference already knew a newer one.-MaxTimeBehind 3Moneeds a release date from the reference. If it is missing, the result isFalsewith the warningNo ReleaseDate found for build 17.0.5035 - cannot determine time-based compliance.-MinimumBuildapplies across all major versions. Against16.0.4295, 2017 and 2019 were not compliant in the Linux lab. With mixed versions, each major version needs its own call.
Write -MinimumBuild with three parts
SQL Server itself reports a four-part version number:
-- Build, product level and remote DAC setting of this instance (read-only)-- MAXDOP 1: runs serially and does not occupy further cores of the applicationSELECT @@SERVERNAME AS server, CAST(SERVERPROPERTY('ProductVersion') AS varchar(20)) AS build, CAST(SERVERPROPERTY('ProductLevel') AS varchar(10)) AS product_level, (SELECT value_in_use FROM sys.configurations WHERE name = 'remote admin connections') AS remote_dacOPTION (MAXDOP 1);server build product_level remote_dac
------ ----- ------------- ----------
SQLSRV-01 16.0.1000.6 RTM 0dbatools works with three parts (16.0.1000). If you copy the number from SQL Server, you get a wrong result for exactly this build:
Test-DbaBuild -SqlInstance SQLSRV-01, SQLSRV-01\SQL2 -MinimumBuild 16.0.1000.6 |
Format-Table SqlInstance, Build, BuildTarget, CompliantSqlInstance Build BuildTarget Compliant
----------- ----- ----------- ---------
SQLSRV-01 16.0.1000 False
SQLSRV-01\SQL2 16.0.1000 False16.0.1000 is less than 16.0.1000.6. So write targets with three parts, the way Get-DbaBuild reports the build. This applies regardless of the operating system.
3 · The build reference decides the result
Get-DbaBuild and Test-DbaBuild do not ask Microsoft. They look things up in a JSON file that the dbatools project maintains and ships with every version. On the first call, dbatools creates a writable copy in the user profile; whichever of the two is newer wins (source code 2.9.1, Get-DbaBuild lines 243–251). Update-DbaBuildReference downloads the current version.
| Reference | As of (LastUpdated) | Entries | newest 2022 build | newest 2025 build |
|---|---|---|---|---|
| shipped with dbatools 2.9.1 | Oct 13, 2026 | 1,172 | 16.0.4305 | 17.0.5015 |
after Update-DbaBuildReference on Nov 18, 2026 | Nov 12, 2026 | 1,184 | 16.0.4325 | 17.0.5065 |
On Windows and Linux, the command downloaded the same version. The project maintains this reference for everyone who uses the module; the Nov 12 version was online one day after its pull request was merged (#10826).
Same servers, two results
In the Linux lab, five instances ran at the patch levels current at the time. Test-DbaBuild -MaxBehind 0CU rated them differently depending on the reference:
| Instance | Build | with the shipped reference | after Update-DbaBuildReference |
|---|---|---|---|
| sql2017 | 14.0.3550 (CU31) | True | True |
| sql2019 | 15.0.4490 (CU32) | True | True |
| sql2022 | 16.0.4295 (CU27) | True | False, target CU28 |
| sql2025 | 17.0.5005 (CU9) | True | False, target CU11 |
| sql2025cu10 | 17.0.5035 (CU10) | True | False, target CU11 |
On Windows the pattern was the same: for 2022 RTM, -MaxBehind 0CU named CU27 as the target with the shipped reference, and CU28 after the update.
So "0 CUs behind" means: current as of the reference. Put its date in your report, and update it before every run:
Update-DbaBuildReference
$ref = Join-Path (Get-DbatoolsConfigValue -FullName Path.DbatoolsData) 'dbatools-buildref-index.json'
(Get-Content $ref -Raw | ConvertFrom-Json).LastUpdatedUnder Windows PowerShell 5.1, the last line returned 2026-11-12T00:00:00.
One copy per user profile
The copy lives in the profile of the user who runs dbatools:
Get-DbatoolsConfigValue -FullName Path.DbatoolsDataC:\Users\Administrator\AppData\Roaming\PowerShell\dbatoolsOn Linux it was under ~/.local/share/PowerShell/dbatools/. If your inventory runs as a scheduled task or Agent job under a service account, that account has its own copy. It only stays current if the job itself calls Update-DbaBuildReference.
dbatools only warns once the reference is older than 45 days (source code 2.9.1, Get-DbaBuild line 264). For the shipped reference from Oct 13, 2026, that warning starts on Nov 27, 2026. Until then, a run without an update silently assesses against the October data.
Without internet access, you only get a warning
In the lab, we pointed the reference source to an unreachable address for one session. Update-DbaBuildReference only reported:
[Get-DbaBuildReferenceIndexOnline] Couldn't download updated index from https://127.0.0.1:9/dbatools-buildref-index.jsonEven with -EnableException, the command did not stop. Get-DbaBuild -Update and Test-DbaBuild -Update then carried on with the old copy. If you check overnight, monitor the LastUpdated value of the copy yourself. For networks without internet access, the source code provides Update-DbaBuildReference -LocalFile; we did not try that.
Builds newer than the reference
SQL Server 2025 CU10 (17.0.5035) was released after Oct 13, 2026, so it is missing from the shipped reference. In the Linux lab, Get-DbaBuild reported:
SqlInstance : sql2025cu10
Build : 17.0.5035
CULevel : CU9
KBLevel : 5126967
ReleaseDate : 10/13/2026 00:00:00
MatchType : Approximate
Warning :The instance shows up as CU9, with the KB number and date of the October security update. The only hint is MatchType Approximate; Warning stays empty. The cause is the search loop: the warning "… not found, closest build we have is …" is only set when a larger entry follows in the reference (source code 2.9.1, lines 342–346). For a build inside the reference, it does appear: on Windows, Get-DbaBuild -Build 16.0.1000.6 reported 16.0.1000.6 not found, closest build we have is 16.0.1000.
Test-DbaBuild at least warns in this case with 17.0.5035 is not recognized as a correct version. With the old reference, CU10 still counted as -Latest compliant, and offline, Test-DbaBuild -MaxBehind 1CU -Update rated it as CU9 against the target CU8. After the update, Get-DbaBuild identified CU10 correctly (Exact). We only reproduced this case in the Linux lab. On Windows, the search loop behaved the same way: a warning only when a larger entry follows. As of Nov 18, 2026, the finding had not been reported in the project.
4 · Databases across all instances
Get-DbaDatabase returns one object per database. Combined with the instance list from section 1 and Export-Csv, that gives you one table for all servers. In the Linux lab, with SQL authentication:
$cred = Get-Credential sa
Get-DbaDatabase -SqlInstance $instances -SqlCredential $cred |
Select-Object SqlInstance, Name, Status, RecoveryModel, Owner, Compatibility, SizeMB, LastFullBackup, LastLogBackup, IsSystemObject |
Export-Csv -Path .\databases.csv -NoTypeInformationAcross five instances, 35 rows came back, including three test databases per instance. Excerpt for SQL Server 2022:
"SqlInstance","Name","Status","RecoveryModel","Owner","Compatibility","SizeMB","LastFullBackup","LastLogBackup","IsSystemObject"
…
"sql2022","inventar_voll","Normal","Full","sa","Version160","16","11/18/2026 05:58:03","11/18/2026 05:58:03","False"
"sql2022","inventar_einfach","Normal","Simple","dbt_besitzer","Version130","16","11/18/2026 05:58:04","01/01/0001 00:00:00","False"
"sql2022","inventar_ohne_sicherung","Normal","Full","sa","Version160","16","01/01/0001 00:00:00","01/01/0001 00:00:00","False"
…What the list shows at a glance:
- Owner:
inventar_einfachis owned by a SQL login, notsa. - Compatibility level: The same database runs at compatibility level 130, that of SQL Server 2016, although the instance is 2022.
- Backups:
inventar_ohne_sicherunguses theFullrecovery model but has never had a full backup.
Missing backups appear as 01/01/0001 00:00:00, not as an empty field. A filter for empty values will not find them. Test such a filter against a database without a backup before you trust the report. How to restore backups rather than just count them is shown in part 4.
Without Select-Object, Get-DbaDatabase also shows IsAccessible, LogReuseWaitStatus, Collation, Encrypted and LastDiffBackup. For Excel instead of CSV, there is the community module ImportExcel; we did not test it. In this round of measurements we only ran Get-DbaDatabase in the Linux lab.
5 · Documenting configuration with Export-DbaInstance
Export-DbaInstance writes the configuration of an instance as scripts into a folder, a new folder per instance and run with a timestamp in its name. This covers sp_configure, logins, server roles, credentials, linked servers, Database Mail, Agent jobs, Extended Events sessions, Resource Governor, policies and registered servers.
One file is not what its name suggests: databases.sql contains no schema, but RESTORE commands from the backup history (according to the help). In the Linux lab (shortened):
RESTORE DATABASE [inventar_voll] FROM DISK = N'/var/opt/mssql/data/inventar_voll.bak' WITH FILE = 1, MOVE N'inventar_voll' TO N'/var/opt/mssql/data/inventar_voll.mdf', … NORECOVERY, NOUNLOAD, REPLACE, STATS = 10RESTORE LOG [inventar_voll] FROM DISK = N'/var/opt/mssql/data/inventar_voll.trn' WITH FILE = 1, … NOUNLOAD, STATS = 10A database without a backup is missing from it, and the paths are those of the source.
Recommended: -ExcludePassword
Locally on the Windows server:
$files = @(Export-DbaInstance -SqlInstance SQLSRV-01 -Path C:\lab\w2\export -ExcludePassword)
if ($files.Count -eq 0) { 'No export for SQLSRV-01' }The call returned 15 files and put them in the folder SQLSRV-01-20261118085342:
Name Length
---- ------
credentials.sql 169
databases.sql 688
dbmail.sql 1365
extendedevents.sql 13784
linkedservers.sql 1653
logins.sql 869
OleDbProvider.sql 4722
policymanagement.sql 2
regserver.xml 3121
replication.sql 586
resourcegov.sql 421
serverroles.sql 1099
sp_configure.sql 4882
sqlagent.sql 9287
userobjectsinsysdbs.sql 0Credentials and linked servers are included, with their passwords replaced by placeholders:
IF NOT EXISTS (SELECT 1 FROM sys.credentials WHERE name = N'dbt_cred') CREATE CREDENTIAL [dbt_cred] WITH IDENTITY = N'dbt_ident', SECRET = N'<EnterStrongPasswordHere>' /* For security reasons the linked server remote logins password is changed with ######## */EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DBT_LINK',@useself=N'False',@locallogin=NULL,@rmtuser=N'dbt_remote',@rmtpassword='########'For documentation that lives in a repository or on a file server, that is the right form. policymanagement.sql was empty at 2 bytes: the server had 16 policies and 17 conditions from Microsoft, but no custom ones. We did not test how the export writes custom policies.
The default call needs the DAC
Without -ExcludePassword, Export-DbaInstance wants to decrypt the passwords of credentials and linked servers. To do that, it first opens a Dedicated Admin Connection and uses it for the entire export (source code 2.9.1, lines 286–304). Locally on Windows the connection succeeded, and both instances returned 14 files. The export was still incomplete:
credentials.sqlcontained the secret of the test credential in plain text.linkedservers.sqldid not contain the linked server password, only########.extendedevents.sqlwas missing, anddatabases.sqlhad 0 bytes.
Two warnings came with it:
[Get-DecryptedObject] Can't unprotect registry data on SQLSRV-01. Do you have administrative access to the Windows registry on SQLSRV-01? Otherwise, we're out of ideas.
[Get-DbaXESession] Failure | An existing connection was forcibly closed by the remote hostWe worked as local administrator. Called on its own, Export-DbaLinkedServer decrypted the same password on the same instance. Why it failed in the full export is open. Our assumption: the shared DAC does not carry all of the sub-steps.
If the DAC is in use, nothing is written
SQL Server allows only one DAC per instance. When another session held it open, the default call stopped immediately:
[Export-DbaInstance] Failure | An existing connection was forcibly closed by the remote hostThere were 0 returned objects and no folder. The SQL Server error log said maximum number of '1' dedicated administrator connections already exists. The command does not throw an error here; it skips the instance and writes a warning (source code, Stop-Function … -Continue). A script that only watches for errors reports success. So check whether files come back, as in the call above.
Passwords only when you need them
If you really need passwords for a move, export them specifically with Export-DbaCredential and Export-DbaLinkedServer. Both also open the DAC. In the lab, they decrypted the secret and the linked server password on the default instance. On the second instance, Export-DbaLinkedServer failed once with Failure | An existing connection was forcibly closed by the remote host and no file; Export-DbaCredential succeeded right after. Treat such files like a secret: not in the repository, not on shares, delete them after use.
For remote Windows servers, according to the help and source code, decryption additionally needs remote admin connections and Windows access to the server (#10549, open). We did not set up that case.
From a Linux client
If dbatools runs on Linux or macOS, for example in a pipeline, more restrictions apply:
- The default call exports nothing. The DAC over the network failed after about 14.5 seconds because
remote admin connectionswas off. The instance was skipped entirely, on all five versions, with the warning[Export-DbaInstance] Failure | Success. - With
-ExcludePassword, 10 files were written. Credentials and linked servers were still missing:[Export-DbaCredential] This command is not supported on Linux or macOS, and the same forExport-DbaLinkedServer. The command skips policies on Linux (source code lines 454–457), and replication reportedCould not load replication libraries. …. - Allowing the remote DAC did not help. On SQL Server 2022 with
remote admin connections = 1, the export took 99 seconds;databases.sqlwas empty andextendedevents.sqlwas missing, as with the default call on Windows. - Side note: On Linux,
regserver.xmlalso ended up next to the export folder as a file with a backslash in its name (sql2017-20261118060021\regserver.xml). On Windows it was placed correctly inside the folder.
As of Nov 18, 2026, the Linux findings on the empty export and on regserver.xml had not been reported in the project. The open #10549 is related.
6 · Find-DbaInstance: for searching, not for inventory
Find-DbaInstance searches the network for SQL Server instances. Depending on the call, it looks up names in Active Directory (SPN), walks an IP range, pings, resolves names, queries the SQL Server Browser (UDP 1434) and the Windows services via WMI, checks TCP ports and, if asked, logs in. That is a network scan. Use it only on your own network and only with permission from the people responsible for that network. The command's own help advises against using it regularly and points to Get-DbaService and Get-DbaRegServer.
We only ran it against our own machine and within our own lab network. On Windows, locally as administrator, without a SQL login:
Find-DbaInstance -ComputerName SQLSRV-01 |
Select-Object ComputerName, InstanceName, Port, Availability, Confidence, ScanTypesComputerName InstanceName Port Availability Confidence ScanTypes
------------ ------------ ---- ------------ ---------- ---------
SQLSRV-01 SQL2 49897 Available High Default
SQLSRV-01 MSSQLSERVER 1433 Available High DefaultBoth instances came back with Confidence High, including the named instance with its dynamic port. The confidence comes from two sources:
-ScanType Browseralone gaveMedium,Availability Unknown.-ScanType SQLServicealone (WMI service query) gaveHigh, but port0.-ScanType Allalso logged in (SqlConnected True).
From a Linux client, there is neither a Browser nor a WMI query. In the lab network, the default scans found the five instances via DNS, ping and port 1433, but only with Confidence Medium and no warning. High only came with -ScanType TCPPort, SqlConnect and credentials. Discovery via -DiscoveryType DataSourceEnumeration failed under PowerShell 7 with Datasource enumeration failed | Unable to find type [System.Data.Sql.SqlDataSourceEnumerator].; on Windows we deliberately did not run it, because it broadcasts into the network.
For your inventory, this means: a hit from Find-DbaInstance is a candidate for the list. The list itself is maintained in the CMS or in the file.
7 · An inventory report you can rely on
- List from one source: a CMS via
Get-DbaRegServeror a file under version control.Find-DbaInstanceonly to look for gaps, with permission. - Reference first:
Update-DbaBuildReferencein the same run and under the same account, then write theLastUpdatedvalue of the copy into the report. - Assess with
Test-DbaBuild:-MaxBehindfor the CU level,-Latestfor security updates,-MinimumBuildwith three parts and per major version. IncludeMatchType Approximateand warnings in the output. - Databases as CSV:
Get-DbaDatabaseacross the whole list; missing backups show up as01/01/0001. - Configuration with
Export-DbaInstance -ExcludePassword: check that files come back. Passwords only when needed, and treated as secrets. - Report unreachable instances: Many commands only write a warning when a connection fails (part 1, part 3). An instance that is missing from the report is a finding in itself.
What we measured and what we did not
- Measured (Nov 18, 2026): dbatools 2.9.1 under PowerShell 7.6.8 in a Linux container against five instances in Linux containers: SQL Server 2017 CU31 and 2019 CU32 (each with the September 2026 security update), 2022 CU27, 2025 CU9 and 2025 CU10. Three small test databases and one job per instance, plus a credential and a linked server with a password on 2022.
Update-DbaBuildReferenceran online once; we simulated the offline case by redirecting the source address, not with a separate network. - Measured on Windows (Nov 18, 2026): dbatools 2.9.1 under Windows PowerShell 5.1 on Windows Server 2022 with two instances of SQL Server 2022 RTM, run locally (client and server on the same machine), Windows authentication as administrator. We tested the build assessment before and after the update,
Export-DbaInstancewith and without-ExcludePasswordand with the DAC in use, the individual password exports, andFind-DbaInstanceagainst the machine itself. The VM had one vCPU; we do not use run times from it. - Only from the source code:
Update-DbaBuildReference -LocalFile, the SPN search ofFind-DbaInstancein Active Directory, decrypting passwords on remote Windows servers. - Not measured: a Central Management Server with
Get-DbaRegServer, Excel output, two separate Windows servers and domains, PowerShell 7 on Windows,Get-DbaDatabaseon Windows, a build newer than the reference and the offline case on Windows, policies with custom content,DataSourceEnumerationon Windows, large estates with many instances.
Further reading
- dbatools: Get-DbaBuild, Test-DbaBuild, Update-DbaBuildReference, Get-DbaDatabase, Export-DbaInstance, Find-DbaInstance, Get-DbaRegServer.
- Microsoft Learn: Diagnostic connection for database administrators (DAC), remote admin connections, Central Management Server.
- At PSG: dbatools overview, Part 1: Install and connect securely, Part 3: What
Test-Dbareally tells you, Part 4: Prove your backups actually restore, SQL Server on Linux and Docker.