Home > download script, powershell, SQLServerPedia Syndication > Automate Downloading SQL Cumulative Updates

Automate Downloading SQL Cumulative Updates

Those of us admins that download each SQL Cumulative Update when it comes out may find this script handy. When you request a CU from Microsoft, you get this plain text email with links and passwords. Each link is to a different self-extracting zip file with the corresponding password below it.

When a CU has many files, it becomes a long series of point-and-click to download them all. Then you have to manually put in the password to each file in order to extract it. I wanted an easier solution.

Requirements:

Usage:

& '.\Download SQL CU.ps1' "hotfix.msg" "C:\Download\SQL CUs\2008\CU2"

Be sure to either run the script in the directory you want to download or provide a second argument.

Update: I had to make a slight tweak to the argument checking.

Script:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Interop.Outlook") | Out-Null
Import-Module BitsTransfer
$7zipPath = "C:\Program Files\7-Zip\7z.exe"
 
if($args.Length -gt 0) {$file = Resolve-Path $args[0]}
else {Write-Host "Must supply a Cumulative Update email in .MSG format"}
 
if($args.Length -gt 1) {$destDir = Resolve-Path $args[1]}
else {$destDir = "."}
Write-Host "Downloading to `"$destDir`""
 
if(-not(Test-Path $7zipPath)) {Write-Host "Path to 7zip must be specified in order to extract"}
 
$ol = New-Object -comObject Outlook.Application
$mail = $ol.Session.OpenSharedItem($file)
$body = $mail.Body
$mail.Close([Microsoft.Office.Interop.Outlook.OlInspectorClose]::olDiscard)
 
$filePasses = $b = $null
$body.Split("`r`n") | ?{$_.startswith("Location") -or $_.startswith("Password")} | %{
    if ($_.StartsWith("Location")) {
        $fileUrl = $_.Substring($_.IndexOf("(")+1, $_.LastIndexOf(")")-$_.IndexOf("(")-1)
        $pass = $null
    }
    if ($_.StartsWith("Password")) {
        $pass = $_ -replace "^Password: "
    }
    if ($fileUrl -ne $null -and $pass -ne $null) {
        $fileName = $fileUrl.Substring($fileUrl.LastIndexOf("/")+1)
        $filePasses += @{$fileName = $pass}
		$filePath = Join-Path $destDir $fileName
        if(-not (Test-Path $filePath)) {
            $fileUrlAbbr = $fileUrl.Substring(0, $fileUrl.IndexOf("/", 7)) + "/.../" + $fileName
            Write-Host "Adding `"$fileUrlAbbr`" to BITS"
            #(New-Object System.Net.WebClient).DownloadFile($fileUrl, $fileName)
            if ($b -eq $null) {
                $b = , (Start-BitsTransfer -Source $fileUrl -Destination $destDir -Suspended -Asynchronous -Priority High -DisplayName $fileName)
            } else {
                #Add-BitsFile -BitsJob $b -Source $fileUrl -Destination $destDir | Out-Null
                $b += Start-BitsTransfer -Source $fileUrl -Destination $destDir -Suspended -Asynchronous -Priority High -DisplayName $fileName
            }
        }
        else {Write-Host "`"$fileName`" already exists"}
    }
}
#$b | Get-BitsTransfer | Format-Table -Property DisplayName, JobState
$b | Resume-BitsTransfer -Asynchronous
 
#loop through transfers and complete any that have finished
#  and resume any that errored
#  recheck every minute
while ($b | Get-BitsTransfer -EA SilentlyContinue) {
    $b | Get-BitsTransfer -EA SilentlyContinue | ?{$_.JobState -eq "Error"} | Resume-BitsTransfer -Asynchronous
    $b | Get-BitsTransfer -EA SilentlyContinue | ?{$_.JobState -eq "Transferred"} | Complete-BitsTransfer
    Start-Sleep -s 60
}
 
# Do file extraction
if(Test-Path $7zipPath) {
    $filePasses.keys | %{
        if(Test-Path $_) {
            if($_.Contains("i386")){$outDir = "-oi386"}
            elseif($_.Contains("x64")){$outDir = "-ox64"}
            elseif($_.Contains("ia64")){$outDir = "-oia64"}
            else{$outDir = "."}
            $pass = "-p" + $filePasses.$_
            & $7zipPath x $_ $pass $outDir "-aos"
        }
    }
}
  1. kiquenet
    August 8th, 2013 at 03:28 | #1

    HOw can I get msg file from any message in Outlook using Powershell?

    • August 13th, 2013 at 10:40 | #2

      You can get the .msg file by saving the email out of Outlook or drag / drop the email to your desktop.

  1. No trackbacks yet.

%d bloggers like this: