Silently Download and Install SQL Server Management Studio (SSMS)

Posted by cviorel on January 27, 2017 Leave a comment (4) Go to comments

SSMS is now free and no longer requiring licensing.
It is a separate install and has a more frequent release cycle, usually around 30 days.
To be able to maintain up to date a larger number of SSMS installations I wrote a PowerShell Script for the task.
Of course that a better approach will be to make the download once and then propagate it to other servers, but for now, this works best for me.

<#
.SYNOPSIS
    Silently Download and Install SQL Server Management Studio (SSMS).
.DESCRIPTION
    This script will download and install the latest available SSMS from Microsoft.
.PARAMETER WriteLog
    You want to log to a file. It will generate more than a few files :)
.EXAMPLE
    .\Get-LastSSMS -WriteLog 1
.NOTES
    Author: Viorel Ciucu
    Date: January, 2017
.LINK
 
#>
 
#Requires -RunAsAdministrator
 
[CmdletBinding()]
param (
    [parameter(Mandatory = $false)]
    [int]$WriteLog = 0
)

if(-not $PSScriptRoot) {
    $PSScriptRoot = Split-Path $MyInvocation.MyCommand.Path -Parent
}
 
$msg = ""
$args = @()
$args += "/install /quiet /norestart" 
 
if($WriteLog -eq 1) { 
    $args += "/log SSMS_$(Get-Date -Format `"yyyyMMdd_HHmm`").txt"
    $msg = "InstallationLog: $PSScriptRoot\SSMS_$(Get-Date -Format `"yyyyMMdd_HHmm`").txt"
}

# Create SSL/TLS secure channel
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

# Start the download
Write-Host "Download starting! Please wait.."
$ProgressPreference = 'SilentlyContinue'
$Domain = "https://msdn.microsoft.com/en-us/library/mt238290.aspx"
$url = (Invoke-WebRequest -Uri $Domain -UseBasicParsing).Links
$members = ($url | Get-Member).Name

$filter = @()
if ($members -match 'innerHTML') {
    $filter = 'innerHTML'
}elseif ($members -match 'outerHTML') {
    $filter = 'outerHTML'
}

$href = ($url | Where-Object $filter -Match "Download SQL Server Management Studio").href | Select-Object -First 1
$job = Start-BitsTransfer -Source $href -DisplayName SSMS -Destination "SSMS-Setup-ENU.exe" -Asynchronous
 
while (($Job.JobState -eq "Transferring") -or ($Job.JobState -eq "Connecting")) { 
    Start-Sleep 5;
} # Poll for status, sleep for 5 seconds, or perform an action.
 
Switch($Job.JobState) {
    "Transferred" { Complete-BitsTransfer -BitsJob $Job; Write-Output "Download completed!" }
    "Error" { $Job | Format-List } # List the errors.
    default { Write-Output "You need to re-run the script, there is a problem with the proxy or Microsoft has changed the download link!"; Exit } # Perform corrective action.
}
 
# We close running SSMS processes
if (Get-Process 'Ssms') {
    Stop-Process -Name Ssms
}
 
# Install silently
Write-Output "Performing silent install..."
Start-Process -FilePath SSMS-Setup-ENU.exe -ArgumentList $args -Wait -Verb RunAs
 
Write-Output $msg
Write-Output "All done!"

.SYNOPSIS

Silently Download and Install SQL Server Management Studio (SSMS).

.DESCRIPTION

This script will download and install the latest available SSMS from Microsoft.

.PARAMETER WriteLog

You want to log to a file. It will generate more than a few files :)

.EXAMPLE

.\Get-LastSSMS -WriteLog 1

.NOTES

Author: Viorel Ciucu

Date: January, 2017

.LINK

#Requires -RunAsAdministrator

[CmdletBinding()]

param (

[parameter(Mandatory = $false)]

[int]$WriteLog = 0

)

if(-not $PSScriptRoot) {

$PSScriptRoot = Split-Path $MyInvocation.MyCommand.Path -Parent

}

$msg = ""

$args = @()

$args += "/install /quiet /norestart"

if($WriteLog -eq 1) {

$args += "/log SSMS_$(Get-Date -Format `"yyyyMMdd_HHmm`").txt"

$msg = "InstallationLog: $PSScriptRoot\SSMS_$(Get-Date -Format `"yyyyMMdd_HHmm`").txt"

}

# Create SSL/TLS secure channel

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

# Start the download

Write-Host "Download starting! Please wait.."

$ProgressPreference = 'SilentlyContinue'

$Domain = "https://msdn.microsoft.com/en-us/library/mt238290.aspx"

$url = (Invoke-WebRequest -Uri $Domain -UseBasicParsing).Links

$members = ($url | Get-Member).Name

$filter = @()

if ($members -match 'innerHTML') {

$filter = 'innerHTML'

}elseif ($members -match 'outerHTML') {

$filter = 'outerHTML'

}

$href = ($url | Where-Object $filter -Match "Download SQL Server Management Studio").href | Select-Object -First 1

$job = Start-BitsTransfer -Source $href -DisplayName SSMS -Destination "SSMS-Setup-ENU.exe" -Asynchronous

while (($Job.JobState -eq "Transferring") -or ($Job.JobState -eq "Connecting")) {

Start-Sleep 5;

} # Poll for status, sleep for 5 seconds, or perform an action.

Switch($Job.JobState) {

"Transferred" { Complete-BitsTransfer -BitsJob $Job; Write-Output "Download completed!" }

"Error" { $Job | Format-List } # List the errors.

default { Write-Output "You need to re-run the script, there is a problem with the proxy or Microsoft has changed the download link!"; Exit } # Perform corrective action.

}

# We close running SSMS processes

if (Get-Process 'Ssms') {

Stop-Process -Name Ssms

}

# Install silently

Write-Output "Performing silent install..."

Start-Process -FilePath SSMS-Setup-ENU.exe -ArgumentList $args -Wait -Verb RunAs

Write-Output $msg

Write-Output "All done!"

Hope you’ll find it useful.

Uncategorized

← Add syntax color to crontab editor

Get SMO version on your server →

4 Comments.

Tracy July 15, 2019 at 8:19 pm

Now that a newer version is out any thoughts on how to uninstall the old version with PowerShell to install the SSMS 18.

cviorel July 20, 2019 at 11:56 am

Hi Tracy,
To uninstall old versions of SSMS I’m using this piece of code which I shamelessly stolen from StackOverflow:

$filter = 'Microsoft SQL Server Management Studio'
$uninstall32 = Get-ChildItem "HKLM:\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall" | ForEach-Object { Get-ItemProperty $_.PSPath } | Where-Object { $_ -match $filter } | Select-Object UninstallString
$uninstall64 = Get-ChildItem "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall" | ForEach-Object { Get-ItemProperty $_.PSPath } | Where-Object { $_ -match $filter } | Select-Object UninstallString

if ($uninstall64) {
    $uninstall64 = $uninstall64.UninstallString -Replace "/uninstall", ""
    $uninstall64 = $uninstall64.Trim()
    Write-Output "Uninstalling..."
    Start-Process "$uninstall64" -ArgumentList "/uninstall /quiet /norestart" -Wait -Verb RunAs
}
if ($uninstall32) {
    $uninstall32 = $uninstall32.UninstallString -Replace "/uninstall", ""
    $uninstall32 = $uninstall32.Trim()
    Write-Output "Uninstalling..."
    Start-Process "$uninstall32" -ArgumentList "/uninstall /quiet /norestart" -Wait -Verb RunAs
}

$filter = 'Microsoft SQL Server Management Studio'

$uninstall32 = Get-ChildItem "HKLM:\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall" | ForEach-Object { Get-ItemProperty $_.PSPath } | Where-Object { $_ -match $filter } | Select-Object UninstallString

$uninstall64 = Get-ChildItem "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall" | ForEach-Object { Get-ItemProperty $_.PSPath } | Where-Object { $_ -match $filter } | Select-Object UninstallString

if ($uninstall64) {

$uninstall64 = $uninstall64.UninstallString -Replace "/uninstall", ""

$uninstall64 = $uninstall64.Trim()

Write-Output "Uninstalling..."

Start-Process "$uninstall64" -ArgumentList "/uninstall /quiet /norestart" -Wait -Verb RunAs

}

if ($uninstall32) {

$uninstall32 = $uninstall32.UninstallString -Replace "/uninstall", ""

$uninstall32 = $uninstall32.Trim()

Write-Output "Uninstalling..."

Start-Process "$uninstall32" -ArgumentList "/uninstall /quiet /norestart" -Wait -Verb RunAs

}

darryl wilson November 20, 2019 at 6:39 pm

Can this be automated on remote server without winrm?
cviorel November 24, 2019 at 1:06 pm

You can use ssh (https://docs.microsoft.com/en-us/windows-server/administration/openssh/openssh_overview).
The bottom line is to have a way to execute commands on a remote system.