A SQL Cmdlet a Day 4 SQLPS

Over the past few days, I have posted about working with SQL using PowerShell, introduction, piping, variables, Each one of these posts demonstrated working with a PowerShell session that was launched from SQL Server Management Studio rather than a Windows PowerShell prompt. If you have attempted any of the code snippets included in the posts within Windows PowerShell, you most likely would have received an error. The reason is that launching PowerShell from SSMS opens a prompt where the SQL Server PowerShell snap-ins have been loaded and registered, while Windows PowerShell does not automatically load these. The short answer is that Windows PowerShell does not automatically include all of the SQL modules that are needed to work with SQL Server.

To demonstrate this, open up a PowerShell prompt from SSMS and let’s take stock of the modules that are automatically loaded:

[appdomain]::CurrentDomain.GetAssemblies() | SELECT FullName

You will notice that there are a number of assemblies that are returned, but which ones are related to SQL? By piping the results to a Where cmdlet and then to our Select, we are able to filter those modules that have “sql” within the FullName:

[appdomain]::CurrentDomain.GetAssemblies() | where {$_.FullName -like "*sqlserver*"} | Select FullName

Kewl, but what’s the difference between this and Windows PowerShell? Running the same cmdlet in a Windows PowerShell prompt shows that there are no assemblies that apply to our filter, having SQL in the FullName:

While working within Windows PowerShell, consider that you want to create a variable that is the localhost default instance of SQL. Running the below cmdlet in Windows PowerShell results in the following error:

$server = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’)  -argumentlist "localhost"

The reason for the error is that the SQL modules references have not been loaded. This does not mean that you can never use a Windows PowerShell prompt to work with SQL, but rather that you must first import the SQL modules. There are several ways of doing this, but the easiest is to call the sqlps utility from within your Windows PowerShell session:

Running this simple command starts a Windows PowerShell 2.0 session with the SQL Server PowerShell provider and cmdlets loaded and registered, which means I can now work with this prompt the same as if I had started it from SSMS. Something that may also jump out is the path of the prompt has changed. Rather than being in the current user directory, C:\Users\David>, the prompt is now in the context of PS SQLSERVER:\>.

As I said this is the easiest means, but it has also been marked as deprecated, which means that we should use our Import-Module cmdlet instead. Several additional steps MUST be taken to successfully import this module:

1. The Windows PowerShell prompt must be run as Administrator
2. The execution policy must allow signed scripts signed by a trusted security provider

Set-ExecutionPolicy RemoteSigned.

Once that has been done, you can call your Import-Module cmdlet:

Import-Module "sqlps" –DiableNameChecking

A couple more hoops to jump through, but the end result is the same. We can now verify the assemblies by again querying the loaded modules:

[appdomain]::CurrentDomain.GetAssemblies() | where {$_.FullName -like "*sqlserver*"} | Select FullName

Now running my cmdlet assigning the localhost default instance to my variable succeeds:

$server = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’)  -argumentlist "localhost"
$server.Databases