Powershell – Modules and Profiles

Probably not technically correct, but pretty close. I’ve been trying to use SQLPSX for Powershell, recently updated to v2.0. For the longest time, I’ve tried various methods to import the modules and couldn’t get them to import successfully. I knew that this was likely due to lack of knowledge so spent a little time tracking down the root cause of my problems.
After quite a bit of searching, I finally figured out why I’ve been having such a hard time loading Powershell modules. By default, the Modules reside in
  %USERPROFILE%\Documents\WindowsPowershell\Modules
I verified that this was set within Powershell with quite a bit of different code snippets.  For some reason, this folder was never created in my Documents folder.  I manually created a folder called “WindowsPowershell” and another inside that called “Modules” and then extracted each of SQLPSX’s module folders into that newly created directory and I finally could use external modules. I restarted Powershell and used

import-module SQLServer

I got a warning that Powershell could not had to set my execution policy to allow remotely signed scripts. That’s not too hard to do, but I couldn’t import new modules without doing that. I ran

Set-ExecutionPolicy remotesigned

and set the default to “Y”. (This is a local test machine so I’m not as concerned about changing this setting.) I re-ran my import-module command and the modules finally imported.

Of course, now I’d like to automatically load these commands whenever I run Powershell, as well as customize the default Powershell environment to pull in the various SQL Powershell modules used in the SQLPS environment that ships with SQL Server 2008.  I found this MSDN article that discusses profiles and how to work with them. You can create a default profile easily with:

new-item -path $profile -itemtype file –force
notepad $profile

This will create a new profile for all users and all shells on that machine. The second command will open up that new file for editing. Place whatever commands you want to run on startup in this file. For example, this article discusses how you can get all of the behavior of the default SQLPS mini-shell. While not optimal, it looks like a great way to get the full power of Powershell w/ the new SQL functionality.