Function to Import the SQLPS PowerShell Module or Snap-in

The SQL Server 2014 basic management tools have been installed on the Windows 8.1 workstation that's being used in this blog article.

When attempting to import the SQLPS (SQL Server PowerShell) module on your workstation, you'll be unable to import it and you'll receive the following error message if the PowerShell script execution policy is set to the default of restricted:

1Import-Module -Name SQLPS

import-sqlmodule1a.jpg

1Import-Module : File C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot
2be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at
3http://go.microsoft.com/fwlink/?LinkID=135170.
4At line:1 char:1
5+ Import-Module -Name SQLPS
6+ ~~~~~~~~~~~~~~~~~~~~~~~~~
7+ CategoryInfo : SecurityError: (:) [Import-Module], PSSecurityException
8+ FullyQualifiedErrorId : UnauthorizedAccess,Microsoft.PowerShell.Commands.ImportModuleCommand

It takes a while for the previous error message to be generated and it's almost like that's one of the last steps that's performed when attempting to import the SQLPS module so I'll plan to check this early on in my function and warn the user as soon as possible.

I recommend setting the script execution policy to RemoteSigned, but you should read the about_Execution_Policies help topic before changing it.

1Set-ExecutionPolicy -ExecutionPolicy RemoteSigned

import-sqlmodule3a.jpg

Once that's done, you'll be able to import the module but you'll receive a warning message about unapproved verbs and the current location is changed. Both are very annoying:

1Import-Module -Name SQLPS

import-sqlmodule2a.jpg

1WARNING: The names of some imported commands from the module ‘SQLPS’ include unapproved verbs that might make them less
2discoverable. To find the commands with unapproved verbs, run the Import-Module command again with the Verbose
3parameter. For a list of approved verbs, type Get-Verb.

You could write a block of code to work around this problem and add it to your scripts:

1if (-not(Get-Module -Name SQLPS)) {
2    if (Get-Module -ListAvailable -Name SQLPS) {
3        Push-Location
4        Import-Module -Name SQLPS -DisableNameChecking
5        Pop-Location
6    }
7}

I've seen other variations of the previous code floating around the Internet, but notice my version uses the best practice of filtering left. No need to grab a list of all the modules on your machine with Get-Module -ListAvailable only to pipe them to Where-Object to filter them down to the SQLPS module. That's inefficient when compared to filtering left by using Get-Module with the Name and ListAvailable parameters.

The problem with adding the previous block of code to all of your SQL related PowerShell scripts is then you'll be adding redundant code to potentially thousands of scripts. Even worse is the nightmare of finding and modifying all of them if you ever need to change that block of code for some reason. Good luck with that or keep reading to learn a better way.

I also want it to load the SQL PSSnapin if the machine has the SQL Server 2008 or 2008 R2 management tools installed instead of the SQL Server 2012 or higher module:

1if (-not(Get-PSSnapin -Name SqlServerCmdletSnapin100, SqlServerProviderSnapin100 -ErrorAction SilentlyContinue)) {
2    if (Get-PSSnapin -Name SqlServerCmdletSnapin100, SqlServerProviderSnapin100 -Registered -ErrorAction SilentlyContinue) {
3        Add-PSSnapin -Name SqlServerCmdletSnapin100, SqlServerProviderSnapin100
4    }
5}

I decided to write a function that would take care of all of that for me and by adding it to a script module, I can simple reference the function from any script that I write:

 1function Import-MrSqlModule {
 2
 3<#
 4.SYNOPSIS
 5    Imports the SQL Server PowerShell module or snapin.
 6
 7.DESCRIPTION
 8    Import-MrSQLModule is a PowerShell function that imports the SQLPS PowerShell
 9    module (SQL Server 2012 and higher) or adds the SQL PowerShell snapin (SQL
10    Server 2008 & 2008R2).
11
12.EXAMPLE
13     Import-MrSqlModule
14
15.NOTES
16    Author:  Mike F Robbins
17    Website: http://mikefrobbins.com
18    Twitter: @mikefrobbins
19#>
20
21    [CmdletBinding()]
22    param ()
23
24    if (-not(Get-Module -Name SQLPS) -and (-not(Get-PSSnapin -Name SqlServerCmdletSnapin100, SqlServerProviderSnapin100 -ErrorAction SilentlyContinue))) {
25    Write-Verbose -Message 'SQLPS PowerShell module or snapin not currently loaded'
26
27        if (Get-Module -Name SQLPS -ListAvailable) {
28        Write-Verbose -Message 'SQLPS PowerShell module found'
29
30            Push-Location
31            Write-Verbose -Message "Storing the current location: '$((Get-Location).Path)'"
32
33            if ((Get-ExecutionPolicy) -ne 'Restricted') {
34                Import-Module -Name SQLPS -DisableNameChecking -Verbose:$false
35                Write-Verbose -Message 'SQLPS PowerShell module successfully imported'
36            }
37            else{
38                Write-Warning -Message 'The SQLPS PowerShell module cannot be loaded with an execution policy of restricted'
39            }
40
41            Pop-Location
42            Write-Verbose -Message "Changing current location to previously stored location: '$((Get-Location).Path)'"
43        }
44        elseif (Get-PSSnapin -Name SqlServerCmdletSnapin100, SqlServerProviderSnapin100 -Registered -ErrorAction SilentlyContinue) {
45        Write-Verbose -Message 'SQL PowerShell snapin found'
46
47            Add-PSSnapin -Name SqlServerCmdletSnapin100, SqlServerProviderSnapin100
48            Write-Verbose -Message 'SQL PowerShell snapin successfully added'
49
50            [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
51            Write-Verbose -Message 'SQL Server Management Objects .NET assembly successfully loaded'
52        }
53        else {
54            Write-Warning -Message 'SQLPS PowerShell module or snapin not found'
55        }
56    }
57    else {
58        Write-Verbose -Message 'SQL PowerShell module or snapin already loaded'
59    }
60
61}

You're probably thinking "That's a lot of code just to load the module or snap-in". Look at the bigger picture! It's not that much when you consider how much space the help and verbose messages are taking up and since I'm only going to write this once, it's actually a lot less code than hard coding just a few lines in hundreds or possibly even thousands of scripts. Not to mention when I need to modify this code, I go to one script module and modify it instead of having to locate and modify those few lines in each of those scripts.

If the SQLPS module or SQL snapin doesn't exist on the machine, you'll receive a warning:

import-sqlmodule4b.jpg

If the execution policy is restricted, this warning will be displayed almost immediately:

import-sqlmodule5a.jpg

Using the Verbose parameter allows you to see exactly what's occurring otherwise no output is generated if no errors occur:

import-sqlmodule6a.jpg

Using verbose output is much better than inline comments because the user of the function can see the messages without having to open the function or script module as in this case and read through possibly thousands of lines of code to find your comments.

Running the same function on a machine with PowerShell version 2 and the SQL 2008R2 client tools loads the SQL PSSnapin and it also loads the SMO (SQL Server Management Objects) .NET assembly:

import-sqlmodule7a.jpg

Why? Because I frequently use PowerShell with SMO and I can't ever remember how to load the necessary assembly since this occurs automatically when using the PowerShell module that's included with the SQL Server 2012 and higher management tools.

The Import-MrSqlModule shown in this blog article is part of my MrSQL PowerShell module which can be downloaded from GitHub.

µ