PowerShell Profile Tips

Automation is one of the purposes for using PowerShell. Automation is a funny thing, however. You can perform steps of automation almost to infinity. If you write a function that replaces 200 lines of code with 1 function call, that’s great. When using that function you find yourself typing out the full function name and possibly populating several parameters. Now you can further automate the process by writing a text file with a JSON object and updating your function to accept the file path to the JSON object instead of several parameters. This way the parameters are stored for future use and less input is required. This process still requires you to type a fair amount of characters so you create an alias for your function, only requiring two characters and the single parameter. But we’re not satisfied yet because the file path to that JSON file is over 100 characters long. This is where you create a variable in your PowerShell profile to store the path. This allows you to type two characters for the function and pass in a single variable. Are we done now?

There are actually more steps that could be taken to make this process even more automated but there are always two questions looming over any automation attempt.

  1. Is the initial cost of developing the automated process going to be less then the long-term savings of the process?
  2. Is the process good enough?

In our example, it could be argued that the initial cost is worth the long-term benefit but, now that our function and single parameter is so easy to type, it would be difficult to argue that it was not good enough. Since it is good enough, efforts to further automate the process should stop and effort be re-allocated to something that will provide more benefit.

With that being said, today I want to cover a couple of basic convenience tips which utilize the Windows PowerShell Profiles.

What are the PowerShell profiles?

There are four PowerShell scripts which execute when you open a new PowerShell console window. In order of execution…

  • %windir%\system32\WindowsPowerShell\v1.0\profile.ps1 This profile applies to all users and all shells.
  • %windir%\system32\WindowsPowerShell\v1.0\ Microsoft.PowerShell_profile.ps1 This profile applies to all users, but only to the Microsoft.PowerShell shell.
  • %UserProfile%\My Documents\WindowsPowerShell\profile.ps1 This profile applies only to the current user, but affects all shells.
  • %UserProfile%\My Documents\WindowsPowerShell\Microsoft.PowerShell_profile.ps1 This profile applies only to the current user and the Microsoft.PowerShell shell.

These profiles are where you can have “pre-scripts” executed to enhance the functionality of your PowerShell session.

Tip 1: Assemblies / Modules

Load all of your most commonly used modules and assemblies in the profile. Here is an example of dependencies that I load in my profile.

Import-Module SQLPS -DisableNameChecking


Modules, like SQLPS, are fairly easy to remember but if you have multiple this can be a drag. As you can see, loading assemblies is even less convenient and takes longer to type out.

Tip 2: Functions / Aliases

My favorite use for the PowerShell profiles is to load custom functions and aliases. I once blogged about a couple of functions that I use to start and save virtual machines with Hyper-V. I put these functions into my profile so that I only have to type Start-MyVMs instead of a dozen lines of code. I also frequently find the need to use different versions of SQL Server utilities. Creating these aliases help me keep track of which version I’m working with.

Set-Alias sqlcmd10 "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe"

Set-Alias sqlcmd11 "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd.exe"

Tip 3: Variables

Another great use for the profiles is to pre-load variables that you use often. A couple examples:

Stored script block to be invoked with the & alias (also an opportunity for a function, depending upon personal preference).

$space = { gci | Select-Object Name, @{Name="Mbytes";Expression={$_.Length/1Mb}} | Sort-Object Mbytes -descending

Save common file path locations.

$scripts = "C:\Scripts\PowerShell"

Finally, you can store strings for T-SQL queries that you chose to run in PowerShell rather than SQL Server Management Studio. I generally use command line if I ever use the dedicated admin connection. In my profile I’d have:

$killAllButMe = "DECLARE @execSql VARCHAR(1000)
SET @execSql = `'`'
SELECT @execSql = @execSql + `'Kill `' + CONVERT(CHAR(10), spid) + `' `'
FROM master.dbo.sysprocesses
WHERE spid <> @@spid

Then I’d call it like this:

Invoke-Sqlcmd -Query $killAllButMe -ServerInstance foo -DedicatedAdministratorConnection

Leave a Reply

%d bloggers like this: