Post
Ask Question

SQL Server 2016 - How To Detect The Current Cumulative Update/Service Pack That Was Installed

In this article, I will explain how to detect the current Cumulative Update / Service Pack installed on your SQL Server.

Detect the Current Cumulative Update / Service Pack Installed

You can use the ServerProperty as a system-defined function to return the SQL Server Instance information like the ProductLevel and ProductUpdateLevel.

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

Example

select SERVERPROPERTY('ProductLevel') as 'Product Level'.

SERVERPROPERTY

The ProductUpdateLevel returns the update level of the current build as CUn where the CU=Cumulative Update.

Example

select SERVERPROPERTY('ProductUpdateLevel') as 'ProductUpdate Level'

ProductUpdate 

Note

The ProductUpdateLevel is available in the SQL Server 2012 through the current version in updates beginning in late 2015.

For more details, check the T-SQL: Get SQL Server Instance Information Using SERVERPROPERTY

Below, we will show how to detect the current Cumulative Update / Service Pack in case the current version doesn't support the ProductUpdateLevel.

Get the current SQL Server Version

You have multiple ways to get the current SQL Server version.

Using SQL Server Management Studio

  • Open SQL Server Management Studio > Connect to SQL Server.
  • Right-click on Server Name > Properties.
  • In the "General" section, check the Version field number.
    SQL Server

Using SQL Server Configuration Manager

  • Open SQL Server Configuration Manager.
  • From the right side, open SQL Server Services.
  • Right-click on the SQL Server Instance name and select Properties.
  • Click on the Advanced Tab and scroll down to the version field number.
    Configuration Manager

Using T-SQL

Select @@version

T-SQL

Using WindowsPowerShell

You can use Windows PowerShell to invoke SQL commands on a reachable server within the network using the Invoke-Sqlcmd cmdlet as follows.

  1. Open Windows PowerShell as Administrator.
  2. Type the Invoke-Sqlcmd with the below parameters.
    • -query: the SQL query that you need to run on the remote server.
    • -ServerInstance: the SQL server instance name.
    • -Username: the username that has sufficient permission to access and execute SQL queries on the remote server.
    • -Password: the password of the elevated user. 
      PS SQLSERVER:\> Invoke-Sqlcmd -query "select SERVERPROPERTY('MachineName') as 'MachineName'" -ServerInstance "epm\epmdb" -Username sa -Password
      MachineName
  3. No matter what method you have used to get the version number, you should now be able to detect the current Cumulative Update / Service Pack, as the following.
  4. Open SQL Server Build Number Table> Search for the copied version number, as shown below.
    Version Number

Applied To

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

Conclusion

In this article, we saw how to get the current Cumulative Update / Service Pack installed in SQL Server.

Reference


Similar Articles