How to get the update level in SQL Server 2016

Consider you need to install a new Cumulative Update or Service Pack on your current SQL Server, but you don’t know what’s the current update level that already installed in your environment!!

In this article, we will explain How to get the update level in SQL Server 2016 and detect the current installed Cumulative Update / Service Pack in SQL Server.

You might also like to read How to Get SQL Server Info Using SERVERPROPERTY?


Detect the Current Cumulative Update Installed in SQL Server 2016

Simply, You can use the ServerProperty as a system defined function to return the SQL Server Instance information and get the update level in SQL Server 2016 through ProductLevel, ProductVersion, and ProductUpdateLevel.

Get the update level in SQL Server 2016

The ProductLevel returns the version level of the SQL Server instance as the following:

  • ‘RTM’ = Original release version
  • ‘SPn = Service pack version
  • ‘CTPn, = Community Technology Preview version
select SERVERPROPERTY('ProductLevel') as 'Product Level'
Detect the Current Cumulative Update / Service Pack

Applies To

  • SQL Server 2008.
  • SQL Server 2012.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

Get the current Cumulative update installed in SQL Server

ProductUpdateLevel used to get the current Cumulative update installed name as CUn.

select SERVERPROPERTY('ProductUpdateLevel') as 'ProductUpdate Level'
How to get the update level in SQL Server 2016

Applies To

  • SQL Server 2012 through the current version of updates beginning in late 2015.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

Get SQL Server Build Number

ProductVersion used to get the product version as major.minor.build.revision.

Example 1

select SERVERPROPERTY('ProductVersion') as 'Product Version'
Get SQL Server Build Number

Example 2

Select @@version
Get the current Cumulative Update - Service Pack installed in SQL Server
  • Once you get the SQL build number, open  SQL Server Build Number Table.
  • Search for the copied version number to get the corresponding name for the SQL version number.
Detect the Current Cumulative Update / Service Pack in SQL Server

Applies To

  • SQL Server 2008.
  • SQL Server 2012.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

You might also like to read How to Get SQL Server Build Numbers?

Run SQL Server Query using PowerShell

You can use Windows PowerShell to invoke SQL command on a reachable server within the network using Invoke-Sqlcmd cmdlet as the following:

  • Open Windows PowerShell as Administrator
  • Type the Invoke-Sqlcmd with the below parameters.
    • query: the SQL query that you need to run on the remote server.
    • ServerInstancethe SQL server instance name.
    • Username: the username that has sufficient permission to access and execute SQL query on the remote server.
    • Password: the password of the elevated user.
Invoke-Sqlcmd -query "select SERVERPROPERTY('MachineName') as 'MachineName'" -ServerInstance "epm\epmdb" -Username sa -Password *****
Run SQL Server Query using PowerShell

Conclusion

In conclusion, we have provided multiple ways to get the Update Level in SQL Server 2016 and to be able to determine the current Cumulative Update / Service Pack installed in SQL Server.

See Also

4 thoughts on “How to get the update level in SQL Server 2016”

  1. Pingback: Supported SQL Server Version for SharePoint 2016 | SPGeeks

  2. Pingback: Extend SQL Server Trial Period more than once | SPGeeks

  3. Pingback: Extend SQL Server Evaluation Period | SPGeeks

  4. Pingback: Auditing in SQL Server | SPGeeks

Leave a Reply