Using SQL Server in Powershell outside of SQLPS
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:
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:
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)
The profile will be run for all users but only for Powershell.exe
This profile will be applied for the given user for all shells.
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"