Before you can use the SMO library, the assemblies need to be loaded. In SQL Server 2012, this step is easier than ever.
In this recipe, we will load the SQLPS module.
Open up your PowerShell console, or PowerShell ISE, or your favorite PowerShell editor.
Type the import-module command as follows:
Import-Module SQLPSConfirm that the module is loaded:
Get-Module
The way to load SMO assemblies has changed between different versions of PowerShell. In PowerShell v1, loading assemblies can be done explicitly using the Load()
or LoadWithPartialName() methods. LoadWithPartialName()
accepts the partial name of the assembly, and loads from the application directory or the Global Assembly Cache (GAC):
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")Although LoadWithPartialName()is still supported and still remains a popular way of loading assemblies, this method should not be used because it will be deprecated in future versions.
Load() requires the fully qualified name of the assembly:
[void][Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")In PowerShell V2, assemblies can be added by using Add-Type:
Add-Type -AssemblyName "Microsoft.SqlServer.Smo"
In PowerShell V3, loading these assemblies one by one is no longer necessary as long as the SQLPS module is loaded:
Import-Module SQLPS
There may be cases where you will still want to load specific DLL versions if you are dealing with specific SQL Server versions. Or you may want to load only specific assemblies without loading the whole SQLPS module. In this case, the Add-Type command is still the viable method of bringing the assemblies in.
When you import the SQLPS module, you might see an error about conflicting or unapproved verbs:
Note
The names of some imported commands from the module SQLPS include unapproved verbs that might make them less discoverable. To find the commands with unapproved verbs, run the Import-Module command again with the Verbose parameter. For a list of approved verbs, type Get-Verb.
This means there are some cmdlets that do not conform to the PowerShell naming convention, but the module and its containing cmdlets are still all loaded into your host. To suppress this warning, import the module with the –DisableNameChecking parameter.