PowerShell Week at SQL University – Post 4


So far we’ve walked though how to turn on PowerShell and add modules, adding SQL Server 2008 snapins and using PowerShell variables, what cmdlets and functions are, what providers are and how to work with methods & properties.


PowerShellProfilesToday we focus on load things in our profile so that they are always available in PowerShell.  First, some basics:

What is a Profile?  A profile is nothing more than a PowerShell script that gets run as soon as you open up a given PowerShell window.

Why would we want to do this?  I use PowerShell almost exclusively for automating work with my SQL Servers.  When I’m ready to do something I don’t want to start by reloading the same toolset every time I open it. I want to click the icon and be ready to go.  The profile saves me from this repetitive loading.  I update my profile as I find cool new scripts that I want to add.

You are not limited to using a single profile in PowerShell.  Personally, I use one of three profiles depending on the task at hand.  Here is how that works. I use the PowerShell ISE or other third party products as my primary way of writing PowerShell scripts (.ps1) and rarely use the “console.”   I only use the console if I need to troubleshoot something that might have to do with STA/MTA.

When I open up the PowerShell ISE it’s uses it’s own profile CurrentUserCurrentHost that is stored in C:\Users\USERNAME\Documents\WindowsPowerShell\Microsoft.PowerShellISE_profile.ps1 (picture-right).  Inside of that file on my machine it currently looks like this:

import-module ISEPack
cd C:\Users\Aaron\Documents\PoSh\Load
. ./invoke-sqlcmd2.ps1
. ./Write-DataTable.ps1
. ./Out-DataTable.ps1
. ./Add-SqlTable.ps1
. ./Export-CSV-Append.ps1
. ./invoke-sqlcommand.ps1
. C:\Users\Aaron\Documents\PoSh\Tasks\Get-DisksSpace.ps1
cd c:\temp\

Whatever is in this file only gets loaded into my session when I open the ISE.  Nothing above gets run when I open up the console.  These are items I use in ISE, but not in the console PowerShell window.

There are items I use in both ISE and the console.  These items live in a “super profile” of sorts since I want these items no matter how I access PowerShell CurrentUserAllHosts (which is located in C:\Users\USERNAME\Documents\WindowsPowerShell\profile.ps1). Profile.ps1 (picture-center) gets run every time I open up any PowerShell “host” including 3rd party tools.  This also includes the SQLPS host which may cause errors messages to appear when you open it or run a SQL Agent Job.  These error messages don’t impact a PowerShell step in a SQL Agent Job from running.

Here’s what I have inside of my Profile.ps1 file.

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Import-Module SQLServer

Whenever I open the ISE both the Microsoft.PowerShellISE_profile.ps1 and the Profile.ps1 scripts get run.

PowerShell.exe (the little blue “host”) has it’s very own CurrentUserCurrentHost profile that it loads.  That’s the exact same name as the profile that we talked about the ISE loading so does that mean that it’s loading that same file?  No.  Each one is configured to load it’s own CurrentUserCurrentHost from a different location.  So if there are items I want in console world only I have a place to store them thanks to C:\Users\USERNAME\Documents\WindowsPowerShell\Microsoft.PowerShell_profile.ps1.  Since I rarely use the console my profile there sat empty until recently.  I only added items after I spotted a post by Oisin Grehan ( blog | twitter ) on Implementing a Matrix-Style Console Screen Saver and I HAD to try it.  This only works in the console and not in the ISE so it made my console profile the perfect place for implementing this.  Now my PowerShell_profile.ps1 file (picture-left) looks like this:

Import-Module screensaver
set-screensavertimeout (new-timespan -minutes 5)

To edit one of the profiles that only load for the console or the ISE all you have to do is open them up and run notepad $profile.  It will figure out which file is the right one for that environment and open it for you.  To edit the profile that is run in every PowerShell environment just run notepad $profile.CurrentUserAllHosts.  If your WindowsPowerShell directory isn’t there it will not automatically create that for you and you will get an error message stating that it “can’t find the specified path“.  I’ve written a little script to check for that and create one for you if it’s not already there:

$myPowerShell = $HOME
$myPowerShell +="\Documents\WindowsPowerShell"
if ((Test-Path $myPowerShell) -eq "true") {"$myPowerShell is already there mate"}
else { "you need to make one"
mkdir $myPowerShell  }

Homework Assignment:

Setup one of your profiles to at least have one of the modules from the SQLPSX project and the SQL Server Snapins loaded up every time you start up the PowerShell ISE. Which profile to use is completely up to you.  Feel free to play around with your choices.  Optionally you can also include the Get-DisksSpace function like I do; but you don’t have to.

Let me know how this goes for you by dropping a note it the comments.  In tomorrow’s post we are going to work with multiple SQL Servers so you’ll need these things up and running for that.

If you’re confused by where your files are you can just run this script that I lifted off of the HeyScriptingGuys site to have it tell you where all of the profiles for your session have been loaded from.  Keep in mind that we’re not touching the ones in the Windows directory.

$profile |

Get-Member -MemberType note* |

Format-Table -Wrap -AutoSize -Property name,

@{ Label=“Path”; Expression={$_.definition -replace “System.String”,“”} }


In Case you would like more to play with…

There is a lot more that can be done with PowerShell Profiles but these are the basics you need to get up and running. If you want to dive deeper check out these posts over on the Hey Scripting Guy Blog.  Go here to download the SQLServer or ISEPack modules; they are both part of larger projects.  If you’d like some of the scripts that I reference in my profile you can grab the Get-DisksSpace one here and I’ll be blogging about the rest later this week.

Be Advised:

Everything that you put into you profile eats up RAM.  Thankfully most scripts are only a few kilobytes.  Keep in mind that the more items you add into your profile the longer that it is going to take to load.

imageIn case you’re wondering if that “Matrix Style Console Screen Saver” is worth having around, here is what it looks like after a few minutes:

That’s all that I’ve got for you today, see you back tomorrow!

Please Share This:

You may also like:

4 Responses

  1. Thanks for the post. I wasn’t aware of the “super-profile” and had been managing different profiles for different tools. Also adding “Set-Alias wtf Get-Help” to my new super profile (thanks Jorge!).

  2. Being the consumate jokester I am, I like to have fun with my programming. I’ve added the following to my profile

    Set-Alias wtf Get-Help

    So now when I get stuck/frustrated inside my shell I can do something like

    wtf get-childitem and I get my help item displayed for whatever commandlet it is I’m trying to use. Stupid and juvenile? Yes. Fun? Absolutely.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.


Subcribe to Blog Via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

What I'm Saying on Twitter

Subscribe via feedburner