Turning a Folder of DBA Scripts into a PowerShell Module

Most DBAs I know have a folder called something like Scripts with forty .ps1 files in it. Half of them start with the same twenty lines of connection code, copied and pasted. One of them has a bug fix that the other nineteen copies don’t. When a colleague asks for the backup-age check, you email them a file, and three months later nobody knows which copy is current.

A woman DBA sorts loose scripts into a navy toolbox with two open drawers of tools and one closed drawer, next to two versions of the same toolbox standing side by side.

A PowerShell module helps with most of that. You put the functions in one file, describe them in a manifest, and load them with Import-Module. The shared connection code lives in one private function. There’s a version number, so you can tell which copy someone has.

I built a small module to see how the pieces behave, and ran each test in Windows PowerShell 5.1 and PowerShell 7.6. The module is called SatDbaTools, and it has two public functions that run read-only queries against two local instances (SQL Server 2019 and SQL Server 2025). For the scripting side of DBA automation, see PowerShell Automation: Backups, Maintenance, and AG Management. This post is about packaging.

The Script Module (.psm1)

A script module is a .psm1 file that holds functions. Here’s mine. Invoke-SatQuery is the shared connection code. The two Get- functions are the public ones.

I used System.Data.SqlClient directly instead of Invoke-Sqlcmd because the SqlServer module isn’t installed on this workstation. That also let me test RequiredModules against a missing module, further down.

Updated 2026-11-04: Invoke-SatQuery now builds its connection string with SqlConnectionStringBuilder and sets Encrypt=True. The first version put the instance name straight into a string and didn’t encrypt the connection. The builder quotes each value, so an instance name can’t add keywords of its own. TrustServerCertificate=True keeps the encryption but tells the driver not to validate the server’s certificate. Microsoft marks that setting as not recommended, and it’s the gap that strict encryption closes to prevent a man-in-the-middle attack.[1] I set it here only because these are local test instances. On a real network, give each server a certificate from a certificate authority your clients trust, and leave TrustServerCertificate off.

Export-ModuleMember on the last line decides what callers see. If a script module has no Export-ModuleMember command, PowerShell exports all of its functions and aliases. Members that aren’t exported can still be read, or run with the call operator, so don’t treat the export list as access control.[2]

I removed that line from a copy, imported the .psm1 by path, and Get-Command -Module listed all three functions, Invoke-SatQuery included. With the line in place, the helper wasn’t listed, and calling it from the session gave “The term ‘Invoke-SatQuery’ is not recognized”. Both versions of PowerShell behaved the same way.

Rows or the Table?

Invoke-SatQuery ends with $table.Rows, so it sends each row down the pipeline on its own. After this post went up, James Brundage (@mrpowershell.com on Bluesky) pointed out that a leading comma keeps the DataTable intact instead.[3] He’s right, and the choice deserves an explanation.

PowerShell unwraps a collection when a function writes it to the pipeline, and it treats a DataTable as a collection of its rows. Write-Output -NoEnumerate sends a collection down the pipeline as a single object.[4] The comma does the same thing a different way. It wraps the table in a one-element array, and PowerShell unwraps that outer array instead of the table.[5] I prefer Write-Output -NoEnumerate $table, because it says what it does.

I ran each form against a DataTable built in memory, in PowerShell 5.1 and 7.6, and both versions gave the same results:

Rows in the table $table or $table.Rows ,$table or Write-Output -NoEnumerate $table
0 $null DataTable
1 one DataRow DataTable
3 an array of three DataRows DataTable

Rows are the right default for this module, because every caller pipes straight into Select-Object. Returning the table would break them. When a function writes the table without enumerating it, Select-Object receives one DataTable instead of three rows, and it looks for an instance property on the table itself. In my test, that gave one object with an empty instance.

Return the table when the caller needs the table itself:

  • The schema: column names and data types from $table.Columns.
  • A reliable count. $table.Rows.Count is 0 when the query returns nothing, while the rows form gives $null.
  • A method or command that takes a whole DataTable.

One more trap: assigning the result to a variable keeps the table, but piping that variable onward unwraps it again. In my test, $result | Measure-Object counted three DataRows, not one DataTable.

James also pointed out that the table brings its own tools.[3] $table.Compute('MAX(hours_since_full)', '') evaluates an aggregate expression over the rows that pass an optional filter.[6] $table.Select('hours_since_full > 24') returns the rows that match a filter, so it does the job of Where-Object, not Select-Object.[7] Both run inside .NET. On a 100,000-row test table, in PowerShell 5.1 and 7.6, they ran between 5 and 80 times faster than Measure-Object and Where-Object in my runs. Inside an advanced function, $PSCmdlet.WriteObject($table, $false) also skips enumeration,[8] and it was 10 to 20 microseconds faster per call than Write-Output -NoEnumerate. I tested all of this in more detail, including a function that offers both with an -AsDataTable switch, in Passing DataTables Through the PowerShell Pipeline (available 2026-12-03).

The Manifest (.psd1)

A manifest is a .psd1 file that holds a hash table describing the module: its version, its root module, what it exports, and what it needs. New-ModuleManifest writes one for you:

Only ModuleVersion is required. The template that New-ModuleManifest generates has a comment above FunctionsToExport that says not to use wildcards, for best performance.[9] I list the two names. The manifest list narrows what the .psm1 exports: when the manifest named two functions and the .psm1 had no Export-ModuleMember at all, Get-Command -Module showed only the two.

The two editions wrote the manifest in different encodings. The file from PowerShell 7.6 starts with the bytes 23 0D 0A, which is UTF-8 with no byte order mark. The file from Windows PowerShell 5.1 starts with FF FE, which is UTF-16 LE. Both are documented.[10][11] Both editions loaded the UTF-8 manifest, which has only ASCII characters. If your source control or diff tool treats UTF-16 as binary, generate the manifest in PowerShell 7 or convert it before you commit it.

Where PowerShell Looks for Modules

Import-Module accepts a path, so you don’t need to install anything while you develop. Installed modules go in a folder listed in $env:PSModulePath, and the default folders differ by edition. PowerShell 7 uses Documents\PowerShell\Modules for the current user and $env:ProgramFiles\PowerShell\Modules for all users. Windows PowerShell 5.1 uses Documents\WindowsPowerShell\Modules and $env:ProgramFiles\WindowsPowerShell\Modules. A module copied into the 7 folders isn’t on the 5.1 path. PowerShell searches those folders recursively, so a module can sit in a version-named subfolder, as in SatDbaTools\1.0.0\. When it finds more than one version, it loads the highest unless you ask for another.[12]

When a module sits under one of the $env:PSModulePath folders, running one of its exported commands imports it automatically. Get-Command with an exact command name also imports the module, and Get-Command with a wildcard doesn’t.[13]

For testing, I didn’t put anything in a real module folder. Each test script prepended my test folder to $env:PSModulePath inside its own process. It also pointed $env:PSModuleAnalysisCachePath at a new file, so every run started with an empty module analysis cache.[14]

This is the module in use, imported by path in PowerShell 7.6. The output from 5.1 was the same apart from column padding. I’ve replaced the workstation and instance names.

The query found no full backup history for master or msdb on the 2025 instance. The OUTER APPLY keeps those databases in the output with no date, and those are the rows the check is meant to report.

Testing FunctionsToExport = ‘*’

I expected the wildcard to make discovery slower. To measure it, I built two pairs of throwaway modules. Each .psm1 writes a line to a log file and then sleeps for three seconds when it loads, so a load shows up both in the log and in the timing. One module in each pair has FunctionsToExport = '*' and the other lists its two functions. I ran each probe in a new process with an empty analysis cache.

In the first pair, the functions are written out in the .psm1. The results were the same for the wildcard and the explicit list, in both editions. Get-Module -ListAvailable and Get-Command with a wildcard pattern found both functions in under 600 ms without running the .psm1. Get-Command with the exact name took about 3,050 ms because it imported the module. With this layout, I measured no timing difference from the wildcard in these single runs.

The second pair uses the layout that many “folder of scripts” modules end up with: each function in its own file under Public\, and a .psm1 that dot-sources them.

With this layout, the results changed. The found and not-found results were the same in 5.1 and 7.6:

Probe FunctionsToExport ‘*’ Explicit list
Get-Module -ListAvailable exported functions none listed both listed
Get-Command with a wildcard pattern nothing found both found
Get-Command with the exact name “not recognized” error found, module imported
Call the function by name in a new session “not recognized” error ran, module imported
Call it after Import-Module ran not tested

With the wildcard and these dot-sourced files, PowerShell couldn’t find the functions until something imported the module by name, so autoloading didn’t work. With an explicit list, discovery worked without loading the module, and the first call loaded it. I didn’t see a timing cost from '*' in either layout.

Reloading While You Develop

Import-Module doesn’t reload a module that’s already loaded. I imported the test module, changed the .SYNOPSIS text in the .psm1, and imported it again. Get-Help still showed the old text. After Import-Module -Force, it showed the new text. The documentation says -Force reloads the root module only. Nested modules and PowerShell classes need Remove-Module or a new session.[15]

Test-ModuleManifest and RequiredModules

Test-ModuleManifest checks that the files a manifest lists are in the paths it gives.[16] It doesn’t check that the functions exist. I made a manifest with a typo, Get-SatInstanceVersoin, in FunctionsToExport. Test-ModuleManifest passed and reported the misspelled name as exported. Import-Module raised no error either, and Get-Command -Module showed one function instead of two. Both editions behaved the same. I’d add a test that counts the exported commands after import.

RequiredModules is where a module declares that it needs the SqlServer module. The SqlServer module comes from the PowerShell Gallery, and SSMS doesn’t install it.[17] I added it to a copy of my manifest:

On this workstation, which doesn’t have the module, both commands failed in both editions:

The import fails before any function runs, with a message that names the missing module. In a module that calls Invoke-Sqlcmd and has no RequiredModules entry, the first sign would be an error when that call runs. Neither message says where to get the module, so put that in your README. I didn’t test the case where the SqlServer module is installed but older than the version the manifest asks for.

Versions Side by Side

I copied the 1.0.0 folder to 1.1.0 and tried to bump the version with Update-ModuleManifest. It refused, because the copied manifest still said 1.0.0 and that didn’t match the folder name. I changed the ModuleVersion line by hand. Bumping the version before copying the folder avoids this.

With both folders on the module path:

A plain import picked 1.1.0, and -RequiredVersion loaded 1.0.0. An Agent job or a scheduled task can pin the version it was tested with while interactive sessions get 1.1.0. If 1.1.0 has a problem, removing its folder should mean new sessions load 1.0.0 again. I didn’t test the removal.

What I Tested and What I Didn’t

  • One workstation, Windows PowerShell 5.1.26100 and PowerShell 7.6.8, two local instances.
  • The discovery timings come from one run per probe. The results that matter here are found or not found, and those were the same in both editions.
  • I didn’t install the SqlServer module, so the RequiredModules results show only the missing-module case.
  • I didn’t publish to a repository or test Install-Module from a private feed.

Try It Yourself

Pick the three scripts you copy most often. Move their shared connection code into one private function, put the three in a .psm1, and list them by name in FunctionsToExport. Then open a new session, prepend the folder to $env:PSModulePath, and run one of the functions without importing anything. If it doesn’t autoload, check the export list first.

If you’ve packaged your own DBA scripts, I’d like to hear how you laid them out and how you share them. Find me on Bluesky or LinkedIn.

I’d be remiss if I didn’t mention the excellent https://dbatools.io/ project which is full of awesome PowerShell tools for DBAs - it’s excellent. Seriously, check it out.

References

  1. TDS 8.0 - Microsoft Learn. Describes TrustServerCertificate=true as not recommended because the driver then doesn’t validate the server TLS/SSL certificate, and states that strict encryption doesn’t allow TrustServerCertificate=true, in order to prevent a man-in-the-middle attack. ↩
  2. Export-ModuleMember - Microsoft Learn. States that a script module with no Export-ModuleMember command exports its functions and aliases, and that members not exported can still be read or run with the call operator. ↩
  3. James Brundage (@mrpowershell.com) - Bluesky. In a reply to this post on 2026-11-04, suggested a leading comma before the returned value so the caller keeps the DataTable, because PowerShell enumerates rows by default. ↩
  4. Write-Output - Microsoft Learn. Write-Output enumerates objects in a collection by default, and the NoEnumerate parameter passes a collection down the pipeline as a single object. ↩
  5. Everything you wanted to know about arrays - Microsoft Learn. PowerShell unwraps arrays in the pipeline and when a function returns them. A leading comma wraps the array in another array so the original survives the unwrapping, and Write-Output -NoEnumerate or return ,$array keep it intact. ↩
  6. DataTable.Compute(String, String) Method - Microsoft Learn. Computes the given expression on the current rows that pass the filter criteria. ↩
  7. DataTable.Select Method - Microsoft Learn. Select(String) gets an array of all DataRow objects that match the filter criteria. ↩
  8. Cmdlet.WriteObject Method - Microsoft Learn. WriteObject(Object, Boolean) writes collection items individually only when enumerateCollection is true, so passing false writes the collection as one object. ↩
  9. New-ModuleManifest - Microsoft Learn. States that only ModuleVersion is required, and shows the generated template comment advising against wildcards in FunctionsToExport for best performance. ↩
  10. about_Character_Encoding (Windows PowerShell 5.1) - Microsoft Learn. States that New-ModuleManifest creates UTF-16LE files in Windows PowerShell. ↩
  11. about_Character_Encoding (PowerShell 7.6) - Microsoft Learn. States that PowerShell 6 and higher defaults to utf8NoBOM for text output. ↩
  12. about_PSModulePath - Microsoft Learn. Lists the default module folders for PowerShell 7 and Windows PowerShell 5.1, describes the recursive search that allows versioned subfolders, and states that the highest version loads by default. ↩
  13. about_Modules - Microsoft Learn. Describes module autoloading, lists Get-Command with a command name as one of the actions that imports a module, and states that Get-Command with a wildcard doesn’t import modules. ↩
  14. about_Environment_Variables - Microsoft Learn. Describes PSModuleAnalysisCachePath, the file that caches data about modules and their commands. ↩
  15. Import-Module - Microsoft Learn. Describes the Force parameter and its limits during development: it reloads the root module, but not nested modules or classes. ↩
  16. Test-ModuleManifest - Microsoft Learn. States that the cmdlet verifies that the files listed in the manifest are in the specified paths. ↩
  17. Download SQL Server PowerShell Module - Microsoft Learn. States that the SqlServer module is distributed through the PowerShell Gallery and that SSMS doesn’t install it. ↩