SQLServerUpdatesModule is a free PowerShell module returns information about the newest available updates for instance SQL Server.
Source for updates list  – http://sqlserverupdates.com

Minimum Requirements

Server
  • SQL Server 2008
  • PowerShell 4.0 with remoting enabled needed on the host for Windows commands
Workstation
  • Windows 7 with PowerShell v4
  • SQL Server 2008 R2 SMO or SSMS

SQLServerUpdatesModule is not required on the server.

I recommend to use the script at client stations, but I encourage to make a test at servers.

 

Install

1. Install from the PowerShell Gallery

Install-Module -Name SQLServerUpdatesModule

The PowerShell Gallery and the command Install-Module are natively available in Windows 10. If you run Windows 7 or 8 or 8.1, you can either install PackageManagement from powershellgallery.com

2. Download the zip directly and manually import the module.

Using module

Get-SQLServerUpdates – returns available updates list  (cumulatvie update, service pack) for SQL Server. Source for updates list  – http://sqlserverupdates.com/

Get-SQLServerUpdateList

Update list for all SQL Server from version 2008 to 2016.

Get-SQLServerUpdateList -Version „SQL Server 2016”

Update list only for SQL Server 2016

Additional information.

When I wrote this script I had a problem with Exception 0x800A01B6. I found solution at this site – link. In the script I use IHTMLDocument3_getElementsByTagName instead of method getElementsByTagName.

Show-SQLServerUpdatesReport  – returns information about deficit of installed updates at instance SQL Server.

Show-SQLServerUpdatesReport -Version ‚13.0.4422.0’

Returns information about deficit of installed updates for version builid SQL Server.

Show-SQLServerUpdatesReport -Version ‚12.0.4502’ | Select -Expand Updates

Returns information about deficit of installed updates for build number SQL Server. Expand properties Updates.

Show-SQLServerUpdatesReport -ServerInstance it-mn-m\mssqlserver14, test-agsqlserver

Returns information about deficit of installed updates for instance with parameter ServerInstance. This command returns objects.

Show-SQLServerUpdatesReport -ServerInstance it-mn-m\mssqlserver14, test-agsqlserver -HTML -Path C:\temp\report.html

Return information about deficit of installed updates for instances with parameter ServerInstance. This command returns report in the format html.

Example report – report.html

Errors and problems

Please send me information about errors and problems by email.

Mateusz Nadobnik

Z pasją poświęcam czas na zdobywanie wiedzy w zakresie szeroko rozumianej Data Platform. Zachwycony językiem skryptowym Windows PowerShell. Swoją wiedzę, doświadczenia i spostrzeżenia opisuję na blogu.

read more

8 komentarzy

  • Reply

    Billy

    26 czerwca, 2018 at 1:45 pm

    When trying to executed Show-SQLServerUpdatesReport -ServerInstance SERVER -HTML and entering a html file name in – I’m getting the following error.

    Cannot index into a null array.
    At C:\Program Files\WindowsPowerShell\Modules\SQLServerUpdatesModule\1.0.0.6\functions\Show-SQLServerUpdatesReport.ps1:216 char:21
    + … if (([double](($Instance.Build) -Replace „\.(.|..)\.”, „”) -g …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : NullArray

    any idea how to fix this? I saw that others are seeing the same error.

    Thanks!

  • Reply

    Mateusz Nadobnik

    26 czerwca, 2018 at 8:01 pm

    Thanks, Billy for your message. I found a bug in the script and I will tomorrow send new version on the https://www.powershellgallery.com/

  • Reply

    Mateusz Nadobnik

    27 czerwca, 2018 at 8:04 am

    The bug with 1.0.0.6 has been fixed. Check it – https://www.powershellgallery.com/packages/SQLServerUpdatesModule/1.0.0.7

  • Reply

    Michael Pettersson

    18 marca, 2019 at 11:04 am

    Hi MATEUSZ
    When I use the dbatools command Get-DbaCmsRegServer and run
    Get-DbaCmsRegServer -SqlInstance fmvms248 -Group ROOT\Test | Select-Object -Unique -ExpandProperty ServerName | Show-SQLServerUpdatesReport

    I get error Cannot index into a null array.
    At C:\Users\mipeadm\Documents\WindowsPowerShell\Modules\SQLServerUpdatesModule\functions\Show-SQLServerUpdatesReport.ps1:248 char:21

    • Reply

      Mateusz Nadobnik

      20 marca, 2019 at 9:14 am

      Hi Micheal
      I tested it and I hadn’t this error.
      Tell my, which version SQLServerUpdateModule do you have
      In your Registered Servers maybe do you have instance with SQL Server 2019?

  • Reply

    Alin Selicean

    4 marca, 2020 at 6:29 am

    Hi Mateusz

    Nice job on this PS module and thanks for sharing it.

    I have the following problem. Using Get-SqlServerUpdate on one of our SQL 2008 R2 (which is at RTM level) instances I am getting the expected result, that it should be patched (ToUpdate reports TRUE). But when using Invoke-SqlServerUpdatesScan I am getting ToUpdate = False. Here is the command I used:

    Invoke-SqlServerUpdatesScan -ServerInstance SERVERNAME

    Name : SERVERNAME
    Product : Microsoft SQL Server
    VersionName : SQL Server 2008
    Edition : Enterprise Edition (64-bit)
    ProductLevel : RTM
    Build : 10.50.1600.1
    LatestUpdate :
    LatestUpdateLink :
    Updates : 10.0.6556
    ToUpdate : False

    I am using v1.1.5.4.

    Can you please shed some light on this as I am out of ideas as to what I may be doing wrong.

    Thanks,
    Alin

  • Reply

    Alin Selicean

    10 marca, 2020 at 12:08 pm

    Hi Mateusz
    Nice job on this. Really useful.

    Is there any chance of getting the output for CumulativeUpdate more useful than it is now? For example, now I am getting stuff like „SP4 ? Download” in this column where it would be more useful to just have SP4 or similar (CUx, RTM, CTPx). The download link is also featured in the Link column anyway, so it’s kinda of duplicate.

    Thanks,
    Alin