How to check SQL Server version?

There are multiple ways to determine SQL Server version. The below list is not complete.
Official article KB321185

Using SQL Server Management Studio

Connect to SQL Server with SSMS and you will see it’s version in Object Explorer Check SQL Server version with SSMS - Object Explorer

It is also possible to view SQL Server version and edition in Server Properties dialog.
Right click on server name in Object Explorer – Properties Check SQL Server version with SSMS - Server Properties Dialog


Queries to get SQL Server version

The easiest way is to run this query:

SELECT @@VERSION
SELECT @@VERSION query

Next query can provide more detailed information including KB number and edition

SELECT
    SERVERPROPERTY('ProductVersion') AS BuildNumber,
	SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('ProductUpdateLevel') AS UpdateLevel,
    SERVERPROPERTY('ProductUpdateReference') AS UpdateReference,
    SERVERPROPERTY('ProductMajorVersion') AS Major,
    SERVERPROPERTY('ProductMinorVersion') AS Minor,
    SERVERPROPERTY('ProductBuild') AS Build
GO
SERVERPROPERTY query
SERVERPROPERTY documentation


Get SQL Server version with sqlcmd utility

This is a console utility which lets you run queries. To get version use this syntax:

sqlcmd -S ServerName\InstanceName -E -Q "SELECT @@VERSION"
Get SQL Server version with SQLSCMD utility (Windows)
-S – server
-E – trusted connection
-Q – query and exit

Interactive mode:
Get SQL Server version with SQLSCMD utility (Windows)

Using sqlcmd in Linux
Get SQL Server version with SQLSCMD utility (Linux)
sqlcmd utility documentation

Get SQL Server version using PowerShell

There are two SQL Server PowerShell Modules:

  • SqlServer: newer, requires PowerShell 5.0
  • SQLPS: no longer being updated
Either of them can be used to get SQL Server version, but these modules are not installed with SSMS starting with version 17.0.
More details in SQL Docs: Install the SQL Server PowerShell module

Check PowerShell Modules

To check if these modules installed use following command:

Get-Module -ListAvailable | Select-String -Pattern sql
Or just try Invoke-Sqlcmd command
Check PowerShell Modules

Install Modules

SqlServer module can be installed with the following command:

Install-Module -Name SqlServer
Next, load the module
Import-Module SqlServer
In case of security errors check this article: Install the SQL Server PowerShell module to set correct execution policy.

Get Version

To get the version use following syntax:

Invoke-Sqlcmd -ServerInstance "ServerName\InstanceName" -Query "SELECT @@VERSION"
Get SQL Server version with PowerShell
Or with SERVERPROPERTY query:
Invoke-Sqlcmd -ServerInstance "ServerName\InstanceName" -Query "SELECT SERVERPROPERTY('ProductVersion') AS BuildNumber,  SERVERPROPERTY('Edition') AS Edition"
Only the build number:
$sqlver=Invoke-Sqlcmd -ServerInstance "ServerName\InstanceName" -Query "SELECT SERVERPROPERTY('ProductVersion')"
$sqlver[0]
Get only SQL Server build number with PowerShell
Or:
Invoke-Sqlcmd -ServerInstance "ServerName\InstanceName" -Query "SELECT SERVERPROPERTY('ProductVersion') as ver" | Select -ExpandProperty ver
Get only SQL Server build number with PowerShell method 2

Get SQL Server version using log files

If you do not want to connect to SQL Server the version can be viewed in error log.

On Windows default error log file location is Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG
Note: ERRORLOG is the file name.
Windows Logs

On Linux default error log location is /var/opt/mssql/log/errorlog
To view the file with less:

less /var/opt/mssql/log/errorlog
SQL Servr logs on Linux
Note: you might want to use sudo or include your user in mssql group:
usermod -g mssql user1

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s