PowerShell Script to Download SQL MCM Videos

imageMicrosoft has made changes to the Microsoft Certified Master program for SQL Server to make it much more accessible to everyone.  In short if you have the required credentials (MCITP: DBA 2008, DBD 2008), the required skills, and $2,500; you too can become an MCM for SQL Server.  Microsoft has partnered with SQLskills to produce 40 hours of introductory MCM training material videos which they have made freely available to everyone. 

If you’d like more training than just the videos SQLskills has some classes that you can attend.

If you’re like me you’ve either already downloaded the videos or you’re not even reading this right now because you clicked the link and just started downloading the videos.  Smile  Well if you don’t want to spend the rest of your day downloading all of those videos by hand I’ve got a little PowerShell script I think you’ll enjoy.

$wc = new-object net.webclient
[regex]$regex ="(?<url>http://download.microsoft.com/download/./././[0-f]{8}-[0-f]{4}-[0-f]{4}-[0-f]{4}-[0-f]{12}/(?<file>[^>]*?wmv))"
[xml]$xml =  $wc.DownloadString("http://www.microsoft.com/feeds/TechNet/en-us/How-to-videos/SQL_Server_2008_Microsoft_Certified_Master_(MCM)_Readiness_Videos.xml")
$xml.rss.channel.item | foreach {
    if ($wc.DownloadString($_.link) -match $regex)
        $url = $matches.url
        $file =  "$home\Videos$($matches.file)"
        if (Test-Path $file)
        {Write-Host "$file is already there mate"}
        else {
            Write-Host "Downloading $file"

This script will check the RSS feed, connect to all of the links in it, find the first .wmv link that it comes across, then copy that file to your videos directory in your documents folder IF it’s not already there.  If you have to stop this script that’s fine, it will figure out which videos have already been downloaded and skip them*.  If you want to try downloading the .MP4 files instead, just swap out .wmv for .mp4 and you should be all set.  A warning about that though; they seem to be a little more than twice the size of the .wmv files

*This script is written for Windows 7 and maybe for Windows Vista.  You’ll have to edit it yourself for Windows XP or just bug Nic Cain to post one 🙂
**IMPORTANT:  I think I have fixed the HTML rendering issues for the code block but you may need to download the script here.

PowerShell First Timers!

First off, welcome to the best addiction that you will ever have!  Since so many people are checking out this post and firing up PowerShell for the first time, I’ve gathered together some useful links.  Here’s one from the ScriptingGuys themselves: How Do I Install PowerShell on Windows 7 and Other Questions 

I have a series of posts you may want to look at to help you get up & running as well as a video I did a while back for MSDN’s geekSpeak:
PowerShell Week Post 0 (5/17)
PowerShell Week Post 1 (5/18)
PowerShell Week Post 2 (5/20)
PowerShell Week Post 3 (5/20)

I’ll blog more later this week about how I put together the script but I wanted to get it into people’s hands ASAP.  Special Thanks go out to Jeremiah Peschka ( blog | twitter ) for not laughing too hard at my first ever attempt at a RegEx as well as Nicolas Cain ( blog | twitter ) & Chad Miller (Blog|Twitter) for giving me pointers on how to use my new RegEx hammer inside of PowerShell.

Please Share This:

Share on facebook
Share on twitter
Share on linkedin

You may also like:

25 Responses

  1. Downloading them using this script to my work laptop and works like a champ. Downloaded the script and opened in the ISE on a Windows XP SP3 machine, changed the file path and away it goes.


    1. Thanks! I have a bunch of examples on here for all kinds of stuff. This script was surprisingly easy to write but it was the RegEx portion that slowed me down. I will post a “How I did this” post next week.

    1. Thanks for the feedback about Chrome Oscar. Good to know that it’s working for some people with Chrome. Maybe it’s a “culture” issue and just doesn’t speak “Palm Tree” 😉

  2. I grabbed the fixed server and all was well with the world. You know, after I changed the path and told it to download the mp4 version for my iPad. 🙂

    1. Thanks for the update. It’s not so much fixed as it’s just not rendered. I’m just not a expert when it comes to HTML tags. Which browser were you using? I tried it in IE yesterday and it worked fine.

  3. Script works fine.

    Just make sure you download the file from the PowerShell Script link. Copy and paste didn’t work for me, I got a regex error.

  4. Thanks Aaron,

    the script you sent me works fine now!
    You did a great job! 🙂

    Keep it real that way, my friend!


  5. Thanks!!! saved me a lot of time..
    I had to change execution policy though before windows allowed me execute it.

  6. Just a quick note, if you change the file pattern to “(?[^>]*?mp4))” It grabs the ipod compatible mp4, since it’s the first in the list. (at least it appears to). This is yet another reason I need to upgrade my scripting skills from bash to powershell. I’ve been in windows longer than linux, but I still haven’t committed the time to learn. Thank you for giving me just one more reason!

    1. Oh thanks for pointing that out Shannon. I meant to tell everyone that they could swap out wmv for zip, mp4, or any other extension that’s up there.

  7. Sorry, doesn’t work for me:

    “Exception callling ‘DownloadFile'” “Path has an invalid format.”

    1. I made a small update to the post because the HTML wasn’t rendering correctly. Luckily Chad Miller just happened to have the answer to that too.

      I’m not sure if that would have caused the error that you got though. You may want to run this “$home\Videos” and just check to make sure that your Videos directory is in the same location. If that doesn’t work just let me know and I’ll investigate further.

  8. If you get the following error on Windows 7, you may be like me!

    Exception calling “DownloadFile” with “2” argument(s): “An exception occurred during a WebClient request.”
    At line:10 char:29
    + $wc.DownloadFile <<<< ($url,$file)
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

    I had changed the location of my video directory, and $home\Videos was pointing to a non-existing directory.

    I updated $file so it points to where I actually squirrel these away and it's working just fine now.

    Thanks Aaron and Nic!

    1. Oh thanks so much for that Kendra! I keep meaning to do a series on just the error messages since even I can’t understand them at first glance the majority of the time.

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

%d bloggers like this: