Turning a Folder of DBA Scripts into a PowerShell Module

2026-11-04 · by Hannah Vernon · in tools

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.

function Invoke-SatQuery {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory)]
        [string]$SqlInstance,

[Parameter(Mandatory)]
        [string]$Query
    )

$connectionString = "Server=$SqlInstance;Database=master;Integrated Security=True;Application Name=SatDbaTools"
    $connection = New-Object System.Data.SqlClient.SqlConnection $connectionString
    try {
        $connection.Open()
        $command = $connection.CreateCommand()
        $command.CommandText = $Query
        $table = New-Object System.Data.DataTable
        $table.Load($command.ExecuteReader())
        $table.Rows
    }
    finally {
        $connection.Dispose()
    }
}

function Get-SatInstanceVersion {
    <#
    .SYNOPSIS
    Returns the version, edition and update level of one or more SQL Server instances.
    #>
    [CmdletBinding()]
    param (
        [Parameter(Mandatory, ValueFromPipeline)]
        [string[]]$SqlInstance
    )

process {
        foreach ($instance in $SqlInstance) {
            $query = '
SELECT
    [instance]       = @@SERVERNAME
    , [version]      = CONVERT(nvarchar(128), SERVERPROPERTY(N''ProductVersion''))
    , [edition]      = CONVERT(nvarchar(128), SERVERPROPERTY(N''Edition''))
    , [update_level] = CONVERT(nvarchar(128), SERVERPROPERTY(N''ProductUpdateLevel''));'
            Invoke-SatQuery -SqlInstance $instance -Query $query |
                Select-Object instance, version, edition, update_level
        }
    }
}

function Get-SatDbBackupAge {
    <#
    .SYNOPSIS
    Returns the hours since the last full backup of each online database.
    #>
    [CmdletBinding()]
    param (
        [Parameter(Mandatory, ValueFromPipeline)]
        [string[]]$SqlInstance
    )

process {
        foreach ($instance in $SqlInstance) {
            $query = '
SELECT
    [database_name]      = [d].[name]
    , [last_full_backup] = CONVERT(varchar(19), [b].[last_full_backup], 120)
    , [hours_since_full] = DATEDIFF(HOUR, [b].[last_full_backup], SYSDATETIME())
FROM
    [sys].[databases] AS [d]
OUTER APPLY
(
    SELECT
        [last_full_backup] = MAX([bs].[backup_finish_date])
    FROM
        [msdb].[dbo].[backupset] AS [bs]
    WHERE
        [bs].[database_name] = [d].[name]
        AND [bs].[type] = ''D''
) AS [b]
WHERE
    [d].[state_desc] = N''ONLINE''
    AND [d].[name] <> N''tempdb''
ORDER BY
    [d].[name];'
            Invoke-SatQuery -SqlInstance $instance -Query $query |
                Select-Object @{ Name = 'instance'; Expression = { $instance } }, database_name, last_full_backup, hours_since_full
        }
    }
}

Export-ModuleMember -Function 'Get-SatInstanceVersion', 'Get-SatDbBackupAge'

function Invoke-SatQuery {

[CmdletBinding()]

param (

[Parameter(Mandatory)]

[string]$SqlInstance,

[Parameter(Mandatory)]

[string]$Query

)

$connectionString = "Server=$SqlInstance;Database=master;Integrated Security=True;Application Name=SatDbaTools"

$connection = New-Object System.Data.SqlClient.SqlConnection $connectionString

try {

$connection.Open()

$command = $connection.CreateCommand()

$command.CommandText = $Query

$table = New-Object System.Data.DataTable

$table.Load($command.ExecuteReader())

$table.Rows

}

finally {

$connection.Dispose()

}

function Get-SatInstanceVersion {

.SYNOPSIS

Returns the version, edition and update level of one or more SQL Server instances.

[CmdletBinding()]

param (

[Parameter(Mandatory, ValueFromPipeline)]

[string[]]$SqlInstance

)

process {

foreach ($instance in $SqlInstance) {

$query = '

SELECT

[instance] = @@SERVERNAME

, [version] = CONVERT(nvarchar(128), SERVERPROPERTY(N''ProductVersion''))

, [edition] = CONVERT(nvarchar(128), SERVERPROPERTY(N''Edition''))

, [update_level] = CONVERT(nvarchar(128), SERVERPROPERTY(N''ProductUpdateLevel''));'

Invoke-SatQuery -SqlInstance $instance -Query $query |

Select-Object instance, version, edition, update_level

}

function Get-SatDbBackupAge {

.SYNOPSIS

Returns the hours since the last full backup of each online database.

[CmdletBinding()]

param (

[Parameter(Mandatory, ValueFromPipeline)]

[string[]]$SqlInstance

)

process {

foreach ($instance in $SqlInstance) {

$query = '

SELECT

[database_name] = [d].[name]

, [last_full_backup] = CONVERT(varchar(19), [b].[last_full_backup], 120)

, [hours_since_full] = DATEDIFF(HOUR, [b].[last_full_backup], SYSDATETIME())

FROM

[sys].[databases] AS [d]

OUTER APPLY

(

SELECT

[last_full_backup] = MAX([bs].[backup_finish_date])

FROM

[msdb].[dbo].[backupset] AS [bs]

WHERE

[bs].[database_name] = [d].[name]

AND [bs].[type] = ''D''

) AS [b]

WHERE

[d].[state_desc] = N''ONLINE''

AND [d].[name] <> N''tempdb''

ORDER BY

[d].[name];'

Invoke-SatQuery -SqlInstance $instance -Query $query |

Select-Object @{ Name = 'instance'; Expression = { $instance } }, database_name, last_full_backup, hours_since_full

}

Export-ModuleMember -Function 'Get-SatInstanceVersion', 'Get-SatDbBackupAge'

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.

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.^[1]

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.

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:

$manifestArgs = @{

Path = '.\modules\SatDbaTools\1.0.0\SatDbaTools.psd1'

RootModule = 'SatDbaTools.psm1'

ModuleVersion = '1.0.0'

Author = 'Hannah Vernon'

Description = 'Read-only DBA helper functions.'

PowerShellVersion = '5.1'

FunctionsToExport = @('Get-SatInstanceVersion', 'Get-SatDbBackupAge')

CmdletsToExport = @()

VariablesToExport = @()

AliasesToExport = @()

}

New-ModuleManifest @manifestArgs

Test-ModuleManifest -Path $manifestArgs.Path

Only ModuleVersion is required. The template that New-ModuleManifest generates has a comment above FunctionsToExport that says not to use wildcards, for best performance.^[2] 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.^[3]^[4] 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.^[5]

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.^[6]

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.^[7]

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.

PS> Get-Command -Module SatDbaTools

CommandType Name Version Source

----------- ---- ------- ------

Function Get-SatDbBackupAge 1.0.0 SatDbaTools

Function Get-SatInstanceVersion 1.0.0 SatDbaTools

PS> '.\SQL2019', '.\SQL2025' | Get-SatInstanceVersion

instance version edition update_level

-------- ------- ------- ------------

SQLDEV01\SQL2019 15.0.4500.2 Developer Edition (64-bit) CU32

SQLDEV01\SQL2025 17.0.5015.4 Enterprise Developer Edition (64-bit) CU9

PS> '.\SQL2019', '.\SQL2025' | Get-SatDbBackupAge | Where-Object database_name -in 'master', 'msdb'

instance database_name last_full_backup hours_since_full

-------- ------------- ---------------- ----------------

.\SQL2019 master 2026-10-25 01:00:08 131

.\SQL2019 msdb 2026-10-25 01:00:20 131

.\SQL2025 master

.\SQL2025 msdb

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.

foreach ($file in Get-ChildItem -Path (Join-Path $PSScriptRoot 'Public') -Filter *.ps1) {

. $file.FullName

}

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.^[8]

Test-ModuleManifest and RequiredModules

Test-ModuleManifest checks that the files a manifest lists are in the paths it gives.^[9] 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.^[10] I added it to a copy of my manifest:

New-ModuleManifest -Path .\SatReq.psd1 -RootModule 'SatReq.psm1' -ModuleVersion '1.0.0' `

-FunctionsToExport 'Get-SatInstanceVersion', 'Get-SatDbBackupAge' `

-RequiredModules @(@{ ModuleName = 'SqlServer'; ModuleVersion = '22.0.0' })

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

Test-ModuleManifest: The specified RequiredModules entry 'SqlServer' in the module manifest '...\SatReq.psd1' is invalid. Try again after updating this entry with valid values.

Import-Module: The required module 'SqlServer' is not loaded. Load the module or remove the module from 'RequiredModules' in the file '...\SatReq.psd1'.

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:

PS> Get-Module -ListAvailable SatDbaTools | Format-Table Name, Version

Name Version

---- -------

SatDbaTools 1.1.0

SatDbaTools 1.0.0

PS> Import-Module SatDbaTools; (Get-Module SatDbaTools).Version.ToString()

1.1.0

PS> Remove-Module SatDbaTools

PS> Import-Module SatDbaTools -RequiredVersion 1.0.0; (Get-Module SatDbaTools).Version.ToString()

1.0.0

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

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. ↩
New-ModuleManifest - Microsoft Learn. States that only ModuleVersion is required, and shows the generated template comment advising against wildcards in FunctionsToExport for best performance. ↩
about_Character_Encoding (Windows PowerShell 5.1) - Microsoft Learn. States that New-ModuleManifest creates UTF-16LE files in Windows PowerShell. ↩
about_Character_Encoding (PowerShell 7.6) - Microsoft Learn. States that PowerShell 6 and higher defaults to utf8NoBOM for text output. ↩
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. ↩
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. ↩
about_Environment_Variables - Microsoft Learn. Describes PSModuleAnalysisCachePath, the file that caches data about modules and their commands. ↩
Import-Module - Microsoft Learn. Describes the Force parameter and its limits during development: it reloads the root module, but not nested modules or classes. ↩
Test-ModuleManifest - Microsoft Learn. States that the cmdlet verifies that the files listed in the manifest are in the specified paths. ↩
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. ↩

Tags: automation, dba-tools, powershell, powershell-7, powershell-modules, scripting, SQL Server, windows-powershell

Turning a Folder of DBA Scripts into a PowerShell Module

The Script Module (.psm1)

The Manifest (.psd1)

Where PowerShell Looks for Modules

Testing FunctionsToExport = ‘*’

Reloading While You Develop

Test-ModuleManifest and RequiredModules

Versions Side by Side

What I Tested and What I Didn’t

Try It Yourself

References

Related

Let us know your thoughts on this postCancel reply

Search

Categories

Pages

Meta

Turning a Folder of DBA Scripts into a PowerShell Module

The Script Module (.psm1)

The Manifest (.psd1)

Where PowerShell Looks for Modules

Testing FunctionsToExport = ‘*’

Reloading While You Develop

Test-ModuleManifest and RequiredModules

Versions Side by Side

What I Tested and What I Didn’t

Try It Yourself

References

Share this:

Related

Let us know your thoughts on this postCancel reply

Search

Categories

Pages

Meta