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 on your environment!!

In this article, we will explain

How to detect the current Cumulative Update / Service Pack that was installed on 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'

Detect the Current Cumulative Update / Service Pack

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

Example

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

Detect the Current Cumulative Update / Service Pack

Note: the ProductUpdateLevel is available at the SQL Server 2012 through 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 through the following ways:

Using SQL Management Studio

  • Open SQL Server Management Studio > Connect to SQL Server.
  • Right Click on Server Name > Properties.
  • In General section > Check the Version field number.
Detect the Current Cumulative Update / Service Pack

Using SQL Configuration Manager

  • Open SQL Server Configuration Manager.
  • From right side > Open SQL Server Services.
  • Right click on SQL Server Instance name > Select Properties.
  • Click on Advanced Tab > Scroll down to version field number.
Detect the Current Cumulative Update / Service Pack

Using T-SQL

Select @@version
 Getting the current Cumulative Update - Service Pack installed - Get Version3

Using WindowsPowerShell

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.
PS SQLSERVER:\> Invoke-Sqlcmd -query "select SERVERPROPERTY('MachineName') as 'MachineName'" -ServerInstance "epm\epmdb" -Username sa -Password *****

Detect the Current Cumulative Update / Service Pack


Whatever which method you have used to get the version number, you should now able to detect the current Cumulative Update / Service Pack, as the following:

Detect the Current Cumulative Update / Service Pack

Applied To
  • SQL Server 2017.
  • SQL Server 2016.
  • SQL Server 2014.
  • SQL Server 2012.
Conclusion

In this article, we have explained How to determine the current Cumulative Update / Service Pack installed in SQL Server.

Reference

SQL Server 2016: Getting the Current Cumulative Update / Service Pack installed

See Also
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s