How do you find SQL Server version/edition without SSMS installed?

Question 1

How do find out which edition is installed without having the management studio installed? I have a server that functions as a license manager for another software. Upon investigation of a high RAM usage alert, I found that the sqlservr.exe process is taking up almost 2 GB of RAM.

I looked through the program menu and found that the configuration manager was installed, otherwise, it is pretty bare bones. I clicked on properties of the EXE file and found 10.50.1600.1, but there is no place that I've found that states whether it is Express, Dev, STN, ENT, etc.

If I had to guess, this is an express edition, but I wanted to know if there is an obvious tell-tale sign.

Update: @Bob - The file tells me what I know, not the edition.

@valo - I get the following error when I run that command and I did verify named pipes was enabled:

HResult 0x35, Level 16, State 1 Named Pipes Provider: Could not open a connection to SQL Server [53]. Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance->specific error has occurred while establishing a connection to SQL Server. Server is not>found or not accessible. Check if instance name is correct and if SQL Server is configured>to allow remote connections. For more information see SQL Server Books Online.. Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

@thomas - I noticed the Stock Keeping Unit Name before I asked the question, but that seemed too easy, I guess my initial suspicion was correct.

Question 2

This can be done through WMI (accessed through PowerShell in my below example). All I'm doing is looking at a property ("SKUNAME") of the SQL Server service, via the SqlServiceAdvancedProperty class. Note, there are a few environment-specific variables that would need to be set accordingly at the top of the code.

$ComputerName = "YourComputerName"
$ServiceName = 'YourEngineServiceName'
$PropertyName = "SKUNAME"
# retrieve the most current version of the ComputerManagement namespace
#
$ComputerManagementNamespace =
 (Get-WmiObject -ComputerName $ComputerName -Namespace "root\microsoft\sqlserver" -Class "__NAMESPACE" |
 Where-Object {$_.Name -like "ComputerManagement*"} |
 Select-Object Name |
 Sort-Object Name -Descending |
 Select-Object -First 1).Name
 
if ($ComputerManagementNamespace -eq $null) {
 Write-Error "ComputerManagement namespace not found"
}
else {
 $ComputerManagementNamespace = "root\microsoft\sqlserver\" + $ComputerManagementNamespace
} 
# get the property and its value
#
Get-WmiObject -ComputerName $ComputerName -Namespace $ComputerManagementNamespace -Class "SqlServiceAdvancedProperty" |
 Where-Object {
 $_.ServiceName -eq $ServiceName -and
 $_.PropertyName -eq $PropertyName
 } |
 Select-Object @{Name = "ComputerName"; Expression = { $ComputerName }},
 ServiceName,
 @{Name = "PropertyValue"; Expression = {
 if ($_.PropertyValueType -eq 0) {
 $_.PropertyStrValue
 }
 else {
 $_.PropertyNumValue
 }
 }}

Likewise, this same information can be found directly in the SQL Server Configuration Manager tool. Once you open it up, right-click on your SQL Server service and go into Properties. Then click on the Advanced tab and look at the Stock Keeping Unit Name key. There will you find what edition you are using.

Question 3

Using the SQL Server Configuration Manager tool did the trick for me. Instead of using the Stock Keeping Unit Name (which just said "Express Edition"), I cross-referenced the File Version with this table: sqlserverbuilds.blogspot.com

Question 4

You can try the command line tool:

C:\>sqlcmd -S [SERVER] -d [DB_NAME] -E -Q "SELECT @@VERSION"

Question 5

If the SQL Server service is running (which based on your description, it is) check the SQL Server error log. You don't need SSMS installed to do this as it is just a text file. File location may vary depending on how it was installed, but Books Online shows the default location here.

The first entry in the log should contain the version/edition information.

Edit: Despite the comment in the question above, this most certainly does tell you the edition:

2014年06月08日 00:05:00.050 spid158 Microsoft SQL Server 2012 (SP1) - 11.0.3381.0 (X64) 
Aug 23 2013 20:08:13 
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

Question 6

Quick Answer

Just run the following:

{InstanceHome}\MSSQL\Binn\sqlservr.exe -v [-s InstanceName]

NOTE ABOUT SQL Server Express LocalDB: LocalDB can only be determined by the existence of the following folder: C:\Program Files\Microsoft SQL Server\{SQL Server Version: 110, 120, etc}\LocalDB\Binn Running "sqlserver.exe -v" doesn't help as it reports as being "Developer Edition", and searching the Registry it reports as being "Express Edition".

This method always works, unlike the other solutions which won't work when:

you don't want to write a program (for example, PowerShell)?
the Client Tools haven't been installed (required to use SQLCMD.EXE)?
There is either no SQL Server log file, or there is no log file that contains any start/restart events?
SQL Server is not running (hence SELECT @@VERSION; is not an option)?

Example 1
If you only have a single Instance installed, you do not need to use the -s switch:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn>sqlservr.exe -v

Returns:

2015年08月01日 11:40:11.63 Server Logging to event log is disabled. Startup option '-v' is supplied, either from the registry or the command prompt.
2015年08月01日 11:40:11.63 Server Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
 May 4 2015 19:11:32
 Copyright (c) Microsoft Corporation
 Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
2015年08月01日 11:40:11.63 Server (c) Microsoft Corporation.
2015年08月01日 11:40:11.63 Server All rights reserved.
2015年08月01日 11:40:11.63 Server Server process ID is 3824.
2015年08月01日 11:40:11.63 Server System Manufacturer: '{CompanyName}', System Model: '{ModelNumber}'.
2015年08月01日 11:40:11.63 Server Authentication mode is MIXED.
2015年08月01日 11:40:11.63 Server The service account is '{AccountName}'. This is an informational message; no user action is required.
2015年08月01日 11:40:11.63 Server SQL Server shutdown has been initiated

Example 2
If you have multiple Instances installed on the machine, you need to also use the -s switch to specify which Instance you are interested in, else it can intermix version info:

C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS2014\MSSQL\Binn>sqlservr.exe -v
 -s SQLEXPRESS2014

Returns:

2015年08月01日 12:01:13.13 Server Logging to event log is disabled. Startup option '-v' is supplied, either from the registry or the command prompt.
2015年08月01日 12:01:13.13 Server Microsoft SQL Server 2014 - 12.0.4213.0 (X64)
 Jun 9 2015 12:06:16
 Copyright (c) Microsoft Corporation
 Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
2015年08月01日 12:01:13.13 Server UTC adjustment: -4:00
2015年08月01日 12:01:13.13 Server (c) Microsoft Corporation.
2015年08月01日 12:01:13.13 Server All rights reserved.
2015年08月01日 12:01:13.13 Server Server process ID is 1712.
2015年08月01日 12:01:13.13 Server System Manufacturer: '{CompanyName}', System Model: '{ModelNumber}'.
2015年08月01日 12:01:13.13 Server Authentication mode is WINDOWS-ONLY.
2015年08月01日 12:01:13.13 Server The service account is '{AccountName}'. This is an informational message; no user action is required.
2015年08月01日 12:01:13.13 Server SQL Server shutdown has been initiated

Additional Information

The quickest, easiest, and most reliable way to determine version information of a program is simply to request that information from the program. Most programs (*.COM and *.EXE) have a command-line switch / flag / parameter / option / dealy-ma-bob that will display help and/or version information.

Depending on the program itself, the command-line switch will be prefixed with one of the following:

[nothing / nada]
-
--
/

And, again depending on the program itself, the command-line switch will be one of the following:

Most common:
- v
- version
Sometimes included in the general help info:
- ?
- h
- help

{ SQL Server examples shown above }

The command-line utility SQLCMD.EXE, on the other hand, uses the -v switch to pass variable values into the SQL script, so it only displays version info in its general info display:

C:\>sqlcmd /?
Microsoft (R) SQL Server Command Line Tool
Version 11.0.2100.60 NT x64
Copyright (c) 2012 Microsoft. All rights reserved.
usage: Sqlcmd [-U login id] [-P password]
 [-S server] [-H hostname] [-E trusted connection]
 ...
 [-v var = "value"...] ...
 ...
 [-? show syntax summary]

NotePad, however, only has a very limited set of command-line switches, and none of them display version info.

Alternative, no-fuss methods:

Check the registry:

Key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\{VersionNumber}\Tools\Setup
Name: Edition
- If you know the version number (e.g. SQL Server 2012 = 110, SQL Server 2014 = 120, etc), you can the Edition for it specifically by running the following at a command prompt:
```
REG QUERY "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\{VersionNumber}\Tools\Setup" /v /e /f Edition
```
  Returns one entry similar to the following:
```
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server110円\Tools\Setup
 Edition REG_SZ Developer Edition
```
- If you what to see the Edition for whatever happens to be installed, run the following at a command prompt:
```
REG QUERY "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server" /s /v /e /f Edition
```
  Returns one or more entries similar to the one shown above and the following:
```
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\Setup
 Edition REG_SZ Express Edition
```
Check the installation log:

C:\Program Files\Microsoft SQL Server\{SQL Server Version: 110, 120, etc}\Setup Bootstrap\Log\{date_time}\Detail.txt

For the string "IsExpressSku":

(03) 2014年09月05日 16:53:44 SQLEngine: --EffectiveProperties: IsExpressSku = True

score 19 · Accepted Answer · 2014-06-10 17:05:26Z

This can be done through WMI (accessed through PowerShell in my below example). All I'm doing is looking at a property ("SKUNAME") of the SQL Server service, via the SqlServiceAdvancedProperty class. Note, there are a few environment-specific variables that would need to be set accordingly at the top of the code.

$ComputerName = "YourComputerName"
$ServiceName = 'YourEngineServiceName'
$PropertyName = "SKUNAME"
# retrieve the most current version of the ComputerManagement namespace
#
$ComputerManagementNamespace =
 (Get-WmiObject -ComputerName $ComputerName -Namespace "root\microsoft\sqlserver" -Class "__NAMESPACE" |
 Where-Object {$_.Name -like "ComputerManagement*"} |
 Select-Object Name |
 Sort-Object Name -Descending |
 Select-Object -First 1).Name
 
if ($ComputerManagementNamespace -eq $null) {
 Write-Error "ComputerManagement namespace not found"
}
else {
 $ComputerManagementNamespace = "root\microsoft\sqlserver\" + $ComputerManagementNamespace
} 
# get the property and its value
#
Get-WmiObject -ComputerName $ComputerName -Namespace $ComputerManagementNamespace -Class "SqlServiceAdvancedProperty" |
 Where-Object {
 $_.ServiceName -eq $ServiceName -and
 $_.PropertyName -eq $PropertyName
 } |
 Select-Object @{Name = "ComputerName"; Expression = { $ComputerName }},
 ServiceName,
 @{Name = "PropertyValue"; Expression = {
 if ($_.PropertyValueType -eq 0) {
 $_.PropertyStrValue
 }
 else {
 $_.PropertyNumValue
 }
 }}

Likewise, this same information can be found directly in the SQL Server Configuration Manager tool. Once you open it up, right-click on your SQL Server service and go into Properties. Then click on the Advanced tab and look at the Stock Keeping Unit Name key. There will you find what edition you are using.

Using the SQL Server Configuration Manager tool did the trick for me. Instead of using the Stock Keeping Unit Name (which just said "Express Edition"), I cross-referenced the File Version with this table: sqlserverbuilds.blogspot.com

Stack Exchange Network

How do you find SQL Server version/edition without SSMS installed?

4 Answers 4

Quick Answer

Additional Information

Linked

Hot Network Questions

How do you find SQL Server version/edition without SSMS installed?

4 Answers 4

Quick Answer

Additional Information

Linked

Related

Hot Network Questions