SQL Judo’s Monthly Challenge – March 2014 – Powershell something


March 2, 2015 by Kenneth Fisher

Every month SQL Judo (Russ Thomas) (b/t) challenges us to do his Monthly DBA Challenge. I’ve decided it would be fun (and good practice) to do them. Another major benefit is that it will force me to move out of my normal comfort zone. For example in the last year he has had challenges on Powershell and Heketon, neither of which I’ve worked with yet.

So far I’ve done

And this time I’m doing:

March 2014 – Powershell something

An open ended challenge this month. Use PowerShell to do something involving SQL Server. The script must include at least one variable, perform a task “on the pipeline”, be able to run unattended (i.e. agent or task scheduler).

This was a hard one for me. As I mentioned above I’ve never worked with powershell before now. So step one, learn me some Powershell! Now obviously I’m not going to be able to become a powershell expert over-night. But at least I can learn the basics. Running a Powershell script, the basic commands, format etc.

Now as it happens I also have a project that I’m doing for my wife that has a few sections that PoSh (Powershell) will be perfect for. First I need to unzip all of the zip files in a given directory and then when I’m done I need to move them into an archive folder. I decided the unzip was a bit too much for me as my first script, so went and found this script: http://simon-may.com/extracting-zip-files-directory-powershell/. This script works quite well for my needs. So archiving all of the files in a given directory is my project.

So step one was to learn the basics. Like anything else there is a whole lot of mid-level information out there. It’s a bit harder to start from the beginning. I was able to find two excellent sources.

  • Midnight DBAs – Jen and Sean McCown have a whole range of videos on various subjects. One of these, of course, is Powershell. They are mostly task based which makes for easy learning. There are a handful that are targeted directly to DBAs although I haven’t had a chance to read them yet.
  • Steve Jones – Steve did a Powershell challenge where he documented his own introduction to Powershell. He has some great pieces on help and basic commands.

I haven’t finished everything from either of these sources yet, although I do plan to. Even with what I read and watched I was able to get enough to get started. It’s actually a fairly straightforward language. I did run into a few issues and got some timely help from Mike Fal (b/t) and I recommend his blog for some interesting and useful PoSh scripts.

So here is my script. The requirements were:

  • Include at least one variable. I used 3 although I could have gotten away with less.
  • Perform a task “on the pipeline”. If I understand this correctly it just means I pipe (|) information from one command to another. Something which is very natural to anyone used to DOS & bat files. In this particular case I’ve piped the output of Get-ChildItem to Where-Object in order to filter it, then piped the information from Where-Object to Move-Item to do the actual file move.
  • Be able to run unattended (i.e. agent or task scheduler). This is actually the last piece of a much longer process all of which will run unattended.
# Specify a variable with the name of the directory holding my 
# load files.  Also create a variable with the formatted date
# that I'll use for my archive directory and last but not least
# the full path for the archive directory.
$dir = "C:\LoadFiles\"
$dateStr = Get-Date -uformat "%Y%m%d_%H%M"
$archiveDir = $dir + $dateStr

# Create the new archive directory.  The -ErrorAction Ignore 
# parameter let's me ignore the error if the directory
# already exists.  I could test for the directory first but 
# this seems easier.
New-Item -ErrorAction Ignore -ItemType directory -Path $archiveDir

# Parse through the load file directory, filter it down to just the
# text and zip files, then move them to the archive directory.
# I tried using the -include parameter but couldn't get multiple
# filters to work.
Get-ChildItem $dir | 
    Where-Object {$_.Extension -eq '.txt' -or $_.Extension -eq '.ZIP'} | 
    Move-Item -Destination $archiveDir

Anyone with any experience in PoSh will notice that I didn’t use any of the obvious aliases. This is deliberate. When learning a new language I prefer to avoid any shortcuts (like aliases) until later in my learning process. This is a personal preference and it’s entirely up to you what you do. However, in order to avoid some of the inevitable “how to do it better” comments let me point out this could also be written.

gci $dir | 
    ? {$_.Extension -eq '.txt' -or $_.Extension -eq '.ZIP'} | 
    mi -Destination $archiveDir

Or any number of other ways. Powershell is massively flexible and I can think of half a dozen ways to re-write just this one little script.

So challenge three completed! Looking forward to number four!

4 thoughts on “SQL Judo’s Monthly Challenge – March 2014 – Powershell something

  1. […] only recently started to play with Powershell (PoSH) but even I’ve begun to discover what a huge number of tasks it can perform. Currently […]

  2. Richard says:

    Yeah, I avoided aliases for a long time. I hope you’ve gone farther with PowerShell. I use it all the time. I think the best way to learn it – or most technologies – is to try and do something with it. I basically learned Powershell writing the script I mention in these blog posts:


    Now I use it to automate all kinds of things – everything from health checks on a SQL Server instance to locking my computer. I alias all kinds of commands, too. I think my PowerShell window is open even more often than my email client of my web browser.

    • I’ve done some more powershell at home but my work environment has a big mix of PoSh versions and limited ability to use them at all (weird security even for DBAs). I’m learning, just slowly 🙂

      • Richard says:

        I worked for a few months at a bank as an ETL programmer and it was extremely hard to work around a lot of the restrictions they put on us. I understand why they needed to be there, but it still gave me a lot of headaches.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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

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

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: