Using SQL Server in Powershell outside of SQLPS

Published 06 September 09 05:54 PM | MartinBell

Up to now I have only shown scripts that use SQL Server’s Powershell minishell (SQLPS.exe) and not the full-blown Powershell (Powershell.exe). If you want to use the scripts I have shown so far, you will need to load the appropriate SQL Server Assemblies. Michael Worries has blogged about which assemblies should be loaded if you wish to use Powershell and SQL Server and has written an initialisation script. If you save this script and start powershell with the recommend command options:

Powershell -NoExit -Command "& '.\Initialize-SqlPsEnvironment.ps1'"

You may get the message:

File C:\powershell\Initialize-SqlPsEnvironment.ps1 cannot be loaded
because the execution of scripts is disabled on this system. Please see "get-help about_signing" for more details.
At line:1 char:2
+ &  <<<< '.\Initialize-SqlPsEnvironment.ps1'

This is because the execution policy is set to restricted. Restricted is the default policy which doesn’t allow any downloaded configuration files or scripts to be loaded or run. There are two ways to overcome this, the one recommended in the error message is to sign the files, but the easiest way (although not necessarily recommended!) is to set the execution policy to a level which allows you to run the script. To do this run powershell as the administrator and then use the command:

set-executionpolicy unrestricted

The Unrestricted policy allows all configuration files to be loaded and any script to run. This obviously has security implications because anyone can then run any script. Using the RemoteSigned policy instead will allow scripts and configuration files downloaded from the internet to be run if signed by a trusted publisher.
If you want all scripts and configuration files to be signed, use the execution AllSigned policy.

If you don’t want to run a script file to load the snapins each time you start up powershell you can instead export the current environment with the export-console command. This allows you to specify as psconsolefile when starting up powershell e.g if the console is exported to the file SQLEnvironment.psc1 in your documents directory start powershell as:

powershell –psconsolefile %USERPROFILE%\Documents\SQLEnvironment.psc1

One difference between the SQLPS minishell and Powershell itself, is the limitiation SQLPS imposes on loading snapins. In the Initialize-SqlPsEnvironment.ps1 two snapins are added with the commands:

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

You can see which snapins have been loaded with the get-PSSnapins command.

Not being able to load addition snapins is a problem if you write you own snapin or want to use a snapin such as SQLPSX.  SQLPSX is written by Chad Miller and can be downloaded from codeplex and has many useful commands that will cut down the amount of code you have to write.  As this will have been downloaded from the internet you will need to unblock the scripts see Chad’s article for SQLServerCentral on how to do this.

If you don’t want to specify command line options when starting Powershell, you can modify one of the profile files to load snapins and create functions that you wish to have for each session. There are 4 possible locations where the default profile can be changed:

This profile is run for all users and all shells (including SQLPS)

%windir%\system32\WindowsPowerShell\v1.0\ Microsoft.PowerShell_profile.ps1
The profile will be run for all users but only for Powershell.exe

%UserProfile%\My Documents\WindowsPowerShell\profile.ps1
This profile will be applied for the given user for all shells.

%UserProfile%\My Documents\WindowsPowerShell\Microsoft.PowerShell_profile.ps1
This profile will be applied for the given user for Powershell.exe

Therefore if you always want to load the SQL Server Assemblies when you run Powershell you can copy Initialize-SqlPsEnvironment.ps1 file using the commands:

REM Make the directory
mkdir  "%UserProfile%\My Documents\WindowsPowerShell"
copy Initialize-SqlPsEnvironment.ps1 "%UserProfile%\My Documents\WindowsPowerShell\Microsoft.PowerShell_profile.ps1"

Filed under:


# Dew Drop – September 7, 2009 | Alvin Ashcraft's Morning Dew said on September 7, 2009 04:50 AM:

Pingback from  Dew Drop &#8211; September 7, 2009 | Alvin Ashcraft&#039;s Morning Dew

# Martin Bell UK SQL Server MVP said on January 24, 2011 10:46 AM:

Have you ever thought about how you would use the result of a SQL query within a Powershell script? If you have searched for the Powershell equivalent of the T-SQL cursor using your favourite search engine may turn up results pertaining to the screen

# Anthony Brown said on April 4, 2011 09:03 PM:

One of the great things with powershell is the ability to easily script out sql server objects. With

This Blog

SQL Blogs