I recently created a PowerShell script to restore SQL Server databases. A customer migration required that I restore 2800 plus databases. As the data directory was in a different location on the new server, this meant using the MOVE clause in the RESTORE DATABASE command to place the data files in the correct directory. This was far too many databases to do this manually. But using PowerShell also meant using a SQL Server module.

Two modules are available for SQL Server: SQLPS and SqlServer. SQLPS is the original module. Although no longer updated, it is still included with SQL Server which Microsoft says is for backwards compatibility. It is also the module that SQL Server Agent automatically loads when a job type (or subsystem) is PowerShell. SqlServer is the new and supported module and is installed from the PowerShell gallery. This is the module Microsoft recommends you use for all new development.

When using PowerShell scripts on a customer server, I generally don’t get the choice of whether SqlServer can be installed. I’m assured that SQLPS will be available when SQL Server is installed, but SqlServer will only be available if the customer has installed it or requests it be installed. If available, SqlServer should be used, but if not, the script must fall back to SQLPS. Therefore, I wrote a code block that determines which modules are available or loaded. If a module is already loaded, that module is used. If SqlServer is available, it will be used over SQLPS. And if SqlServer is not available, SQLPS is used.

This will only work when using cmdlets included in both SqlServer and SQLPS. If using a cmdlet that exists in only SqlServer, then that is the module that must be used. This code block is provided AS IS with no warrantee of any kind.

# LoadSqlModule.ps1
#
# Use this code block to load a SQL Server PowerShell module. Currently, two modules
# are available:
#
#    SQLPS       Original module that is included with SQL Server for backward
#                compatibility but is no longer updated.
#
#    SqlServer   Current version available from PowerShell Gallery. Because it
#                supports versioning and requires PowerShell 5.0(version 21.1 and
#                earlier) or PowerShell 5.1 (version 22.0 and later).
#
# SQL Server Agent will automatically load SQLPS when a job step type (subsystem) is
# PowerShell. To use module SqlServer instead, SQLPS must first be unloaded using
# the Remove-Module cmdlet.  Starting with SQL Server 2019, automatic loading of
# SQLPS will be disabled if the first line of a job step is #NOSQLPS.
#
# NOTE: SqlServer 22.0 has started the transistion to Strict Connection Encryption.
# This changed how encryption is negotiated during the initial connection phase. It
# now walks the the cerificate path to ensure it was issued by a trusted root
# certificate authority, which must be either a trusted third-party certificate
# authority or an Active Directory Certificate Authority. A self-signed certificate,
# which SQL Server uses by default if another certificate is not installed, is no
# longer implicitly trusted. For now, use of Strict Connection Encryption is
# enforced only for Invoke-Sqlcmd and optional for the other cmdlets. Parameter
# -Encrypt was added in version 22.0 and may be Mandatory, Optional, or Strict.
#
# Written by Charles Rutledge - Revised September 2, 2024

# -- Ordered collection of known SQL Server modules in order of preference. Initial
#    assumption is all modules are not available (installed).
$SqlModules = [ordered]@{ SqlServer = 'NotAvailable'; SQLPS = 'NotAvailable' }
# -- Determine is any of the modules are available or loaded.
foreach ($module in $SqlModules.Keys.Clone()) {
    if ((Get-Module -Name $module -ListAvailable) -ne $null) { $SqlModules[$module] = 'Available' }
    if ((Get-Module -Name $module) -ne $null) { $SqlModules[$module] = 'Loaded' }
}
if (($SqlModules.Values -contains 'Available') -or ($SqlModules.Values -contains 'Loaded')) {
	# -- If an avaiable module is not loaded, attempt to load one in order of
	#    preference.
    if (($SqlModules.Values -contains 'Loaded') -ne $true) {
        foreach ($module in $SqlModules.Keys) {
            if ($SqlModules[$module] -eq 'Available') {
                Write-Host ('Loading PowerShell module ' + $module + ' ...') -ForegroundColor Yellow
                Import-Module -Name $module
                break
            }
        }
    # -- Found one of the modules is already loaded.
    } else {
        foreach ($module in $SqlModules.Keys) {
            if ($SqlModules[$module] -eq 'Loaded') { 
                Write-Host ('Using loaded PowerShell module ' + $module + ' ...') -ForegroundColor Yellow
            }
        }
    }
# -- None of the modules are available (installed) and execution can't continue.
} else {
    Write-Host ('No PowerShell module for SQL Server found.') -ForegroundColor Red
    Exit
}

Issues with installing SqlServer (it’s not straightforward when SQLPS is already installed), breaking changes with the move to Strict Connection Encryption, and the PowerShell script to restore databases will be subjects of future articles.