SQLServer 版本之八大方法搞清“我是谁”

admin

2021年4月24日 8:36 本文热度 4293

你正在使用 SQL Server 的哪个版本？

贴士:作为一个SQL Server数据库管理者或维护、支持人员，应该会经常问自己这样一个问题：我当前SQL Server版本号是？当前版本已经有的累计更新、安全更新包有哪些？这么多包要选哪个？等等，会遇到类似心烦的问题。这里给大家梳理一下关于如何方便的获取SQL Server数据库版本信息，希望在日常运维中有所帮助。

_{介绍方法前，版本信息中相关名词的概念稍作解释，便于版本信息解读}

1. The edition 版本，如：企业版、标准版等

2. The product version 标准产品版本号MM.nn.bbbb.rr

如： 10.0.1600.22-------major.minor.build.revision

MM - 主版本--major
nn - 次版本 minor
bbbb - 内部版本号 build
rr - 内部修订版本号 revision

3. The ProductMajorVersion产品主版本号如：

12为 SQL SERVER 2014

11为 SQL SERVER 2012

10.5为SQL SERVER 2008R2

10 为SQL SERVER 2008

9为SQL SERVER 2005

4. The product level 实例版本级别，如：

'RTM' = Original release version 最初发布版本

SPn' = Service pack version 服务包版本

'CTPn', = Community Technology Preview version 社区技术预览版

5. The ProductBuildType 产品当前构建类型，如

OD = OD 为特殊客户发布到web

GDR = GDR 通过windows更新发布

NULL = Not applicable 无

6. The ProductBuild 产品构建，如：4425

7. The ProductUpdateLevel 产品更新级别，如

CUn = Cumulative Update累计更新，如CU1、CU2、CU3

NULL = Not applicable 无

8. The ProductUpdateReference 产品更新参考

如：KB3094221

9. The ProductMinorVersion 次版本号

获取SQL Server 及其组件的版本、版本类别和更新级别的八种方法

方法一：

连接SQL Server Management Studio利用Object Explorer显示的主要版本号信息，如图（一）显示当前实例产品版本号事11.0.6020

图（一）

方法二：

通过日志获取版本号，打开SQLServer默认安装目录的Log文件下找到ErrorLog文件如图（二），打开后可看到版本信息如（图三），此方法适合SQLServer服务停止情况下查看版本号信息

使用SQLcmd命令查看SQL server的版本，运行sqlcmd.exe打开sqlcmd命令窗口，然后执行1>sqlcmd select @@version，2>go

可以看出，@@version 版本号结果中还包含 OS 版本信息。

方法三：

图（二）

图（三）

或者打开SSMS 在SQLSERVER代理下通过日志查看器查看，如下图（四）所示：

图（四）

方法四：

通过执行select @@version获取版本号信息，如下图（五）所示,

图（五）

方法五：

在 SQL Server Management Studio 中通过执行[sys].[xp_msver]获取版本号信息，如下图（六）所示

图（六）

方法六：

在 SQL Server Management Studio 中通过执行下面脚本获取版本号信息，结果如下图（七）所示

SELECT
SERVERPROPERTY('BuildClrVersion') AS BuildClrVersion --Version of the Microsoft.NET Framework common language runtime (CLR) that was used while building the instance of SQL Server.
,SERVERPROPERTY('Collation') AS Collation --Name of the default collation for the server.
,SERVERPROPERTY('CollationID') AS CollationID --ID of the SQL Server collation.
,SERVERPROPERTY('ComparisonStyle') AS ComparisonStyle --Windows comparison style of the collation.
,SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ComputerNamePhysicalNetBIOS --NetBIOS name of the local computer on which the instance of SQL Server is currently running.
,SERVERPROPERTY('Edition') AS Edition --Installed product edition of the instance of SQL Server. Use the value of this property to determine the features and the limits, such as Compute Capacity Limits by Edition of SQL Server. 64-bit versions of the Database Engine append (64-bit) to the version.
,SERVERPROPERTY('EditionID') AS EditionID --EditionID represents the installed product edition of the instance of SQL Server. Use the value of this property to determine features and limits, such as Compute Capacity Limits by Edition of SQL Server.
,SERVERPROPERTY('EngineEdition') AS EngineEdition --Database Engine edition of the instance of SQL Server installed on the server.
,SERVERPROPERTY('HadrManagerStatus') AS HadrManagerStatus --Applies to: SQL Server 2012 through SQL Server 2016. Indicates whether the AlwaysOn Availability Groups manager has started.
,SERVERPROPERTY('InstanceDefaultDataPath') AS InstanceDefaultDataPath --Applies to: SQL Server 2012 through current version in updates beginning in late 2015.Name of the default path to the instance data files.
,SERVERPROPERTY('InstanceDefaultLogPath') AS InstanceDefaultLogPath --Applies to: SQL Server 2012 through current version in updates beginning in late 2015.Name of the default path to the instance data files.
,SERVERPROPERTY('InstanceName') AS InstanceName --Name of the instance to which the user is connected.
,SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS IsAdvancedAnalyticsInstalled --Returns 1 if the Advanced Analytics feature was installed during setup; 0 if Advanced Analytics was not installed.
,SERVERPROPERTY('IsClustered') AS IsClustered --Server instance is configured in a failover cluster.
,SERVERPROPERTY('IsFullTextInstalled') AS IsFullTextInstalled --The full-text and semantic indexing components are installed on the current instance of SQL Server.
,SERVERPROPERTY('IsHadrEnabled') AS IsHadrEnabled --Applies to: SQL Server 2012 through SQL Server 2016.AlwaysOn Availability Groups is enabled on this server instance.
,SERVERPROPERTY('IsIntegratedSecurityOnly') AS IsIntegratedSecurityOnly --Server is in integrated security mode.
,SERVERPROPERTY('IsLocalDB') AS IsLocalDB --Applies to: SQL Server 2012 through SQL Server 2016.Server is an instance of SQL Server Express LocalDB.
,SERVERPROPERTY('IsPolybaseInstalled') AS IsPolybaseInstalled --Applies to: SQL Server 2016.Returns whether the server instance has the PolyBase feature installed.
,SERVERPROPERTY('IsSingleUser') AS IsSingleUser --Server is in single-user mode.
,SERVERPROPERTY('IsXTPSupported') AS IsXTPSupported --Applies to: SQL Server (SQL Server 2014 through SQL Server 2016), SQL Database.Server supports In-Memory OLTP.
,SERVERPROPERTY('LCID') AS LCID --Windows locale identifier (LCID) of the collation.
,SERVERPROPERTY('LicenseType') AS LicenseType --Unused. License information is not preserved or maintained by the SQL Server product. Always returns DISABLED.
,SERVERPROPERTY('MachineName') AS MachineName --Windows computer name on which the server instance is running.
,SERVERPROPERTY('NumLicenses') AS NumLicenses --Unused. License information is not preserved or maintained by the SQL Server product. Always returns NULL.
,SERVERPROPERTY('ProcessID') AS ProcessID --Process ID of the SQL Server service. ProcessID is useful in identifying which Sqlservr.exe belongs to this instance.
,SERVERPROPERTY('ProductBuild') AS ProductBuild --Applies to: SQL Server 2014 beginning October, 2015. The build number.
,SERVERPROPERTY('ProductBuildType') AS ProductBuildType --Applies to: SQL Server 2012 through current version in updates beginning in late 2015. The build Type.
,SERVERPROPERTY('ProductLevel') AS ProductLevel --Level of the version of the instance of SQL Server.
,SERVERPROPERTY('ProductMajorVersion') AS ProductMajorVersion --Applies to: SQL Server 2012 through current version in updates beginning in late 2015. The major version.
,SERVERPROPERTY('ProductMinorVersion') AS ProductMinorVersion --Applies to: SQL Server 2012 through current version in updates beginning in late 2015. The minor version.
,SERVERPROPERTY('ProductUpdateLevel') AS ProductUpdateLevel --Applies to: SQL Server 2012 through current version in updates beginning in late 2015.
,SERVERPROPERTY('ProductUpdateReference') AS ProductUpdateReference --Applies to: SQL Server 2012 through current version in updates beginning in late 2015.
,SERVERPROPERTY('ProductVersion') AS ProductVersion --Version of the instance of SQL Server, in the form of'major.minor.build.revision'.
,SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime --Returns the date and time that the Resource database was last updated.
,SERVERPROPERTY('ResourceVersion') AS ResourceVersion --Returns the version Resource database.
,SERVERPROPERTY('ServerName') AS ServerName --Both the Windows server and instance information associated with a specified instance of SQL Server.
,SERVERPROPERTY('SqlCharSet') AS SqlCharSet --The SQL character set ID from the collation ID.
,SERVERPROPERTY('SqlCharSetName') AS SqlCharSetName --The SQL character set name from the collation.
,SERVERPROPERTY('SqlSortOrder') AS SqlSortOrder --The SQL sort order ID from the collation
,SERVERPROPERTY('SqlSortOrderName') AS SqlSortOrderName --The SQL sort order name from the collation.
,SERVERPROPERTY('FilestreamShareName') AS FilestreamShareName --The name of the share used by FILESTREAM.
,SERVERPROPERTY('FilestreamConfiguredLevel') AS FilestreamConfiguredLevel --The configured level of FILESTREAM access. For more information, see filestream access level.
,SERVERPROPERTY('FilestreamEffectiveLevel') AS FilestreamEffectiveLevel --The effective level of FILESTREAM access. This value can be different than the FilestreamConfiguredLevel if the level has changed and either an instance restart or a computer restart is pending. For more information, see filestream access level.
GO