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
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
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
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:
If the execution policy is restricted, this warning will be displayed almost immediately:
Using the Verbose
parameter allows you to see exactly what's occurring otherwise no output is
generated if no errors occur:
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:
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.
µ