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

• SQL Judo’s Monthly Challenge – January 2014 – Restore the Master
• SQL Judo’s Monthly Challenge – February 2014 – KILL with the DAC

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!

Filed under: Microsoft SQL Server, Powershell, SQL Judo's Monthly DBA Challenge, SQLServerPedia Syndication Tagged: code language, Powershell, SQL Judo's Monthly DBA Challenge, Steve Jones