SQLvariations: SQL Server, a little PowerShell, maybe some Hyper-V Rotating Header Image

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"
            $wc.DownloadFile($url,$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*.

*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 have fixed the HTML rendering issues for the code block but you may want 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.

4 Comments

  1. GS says:

    Hi ..

    for me the script completes without any error .. in minutes .. BUT do not download any files ..

    tried on XP sP3 and windows 7 (using PS ISE ) have created requred folder in $home path …

    let me know whts missiing in my case

  2. Rob Gilbert says:

    Great script thank you & thank you for your talk to the South West SQL User group in the UK on Thursday, some great tips, I really enjoyed it
    Regards
    Rob

  3. Boyer says:

    Thanks a lot for this script.

  4. Genaro Mariscal says:

    This is a great script man. Thank you very much.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>