你正在使用 SQL Server 的哪個(gè)版本？  

貼士:作為一個(gè)SQL Server數(shù)據(jù)庫管理者或維護(hù)、支持人員，應(yīng)該會經(jīng)常問自己這樣一個(gè)問題：我當(dāng)前SQL Server版本號是？當(dāng)前版本已經(jīng)有的累計(jì)更新、安全更新包有哪些？這么多包要選哪個(gè)？等等，會遇到類似心煩的問題。這里給大家梳理一下關(guān)于如何方便的獲取SQL Server數(shù)據(jù)庫版本信息，希望在日常運(yùn)維中有所幫助。

_{介紹方法前，版本信息中相關(guān)名詞的概念稍作解釋，便于版本信息解讀} 

1. The edition 版本，如：企業(yè)版、標(biāo)準(zhǔn)版等

2. The product version  標(biāo)準(zhǔn)產(chǎn)品版本號MM.nn.bbbb.rr

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

• MM - 主版本--major
• nn - 次版本 minor
• bbbb - 內(nèi)部版本號 build
• rr - 內(nèi)部修訂版本號 revision 

3. The ProductMajorVersion產(chǎn)品主版本號 如：

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  實(shí)例版本級別，如：

'RTM' = Original release version 最初發(fā)布版本

SPn' = Service pack version 服務(wù)包版本

'CTPn', = Community Technology Preview version 社區(qū)技術(shù)預(yù)覽版 

5. The ProductBuildType 產(chǎn)品當(dāng)前構(gòu)建類型，如

OD = OD  為特殊客戶發(fā)布到web

GDR = GDR 通過windows更新發(fā)布

NULL = Not applicable 無

 6. The ProductBuild     產(chǎn)品構(gòu)建，如：4425 

7. The ProductUpdateLevel         產(chǎn)品更新級別，如

CUn = Cumulative Update累計(jì)更新，如CU1、CU2、CU3

NULL = Not applicable 無 

8. The ProductUpdateReference      產(chǎn)品更新參考

如：KB3094221 

9. The ProductMinorVersion     次版本號 

獲取SQL Server 及其組件的版本、版本類別和更新級別的八種方法 

---

方法一：

連接SQL Server Management Studio利用Object Explorer顯示的主要版本號信息，如圖（一）顯示當(dāng)前實(shí)例產(chǎn)品版本號事11.0.6020

圖（一）

方法二：

通過日志獲取版本號，打開SQLServer默認(rèn)安裝目錄的Log文件下找到ErrorLog文件如圖（二），打開后可看到版本信息如（圖三），此方法適合SQLServer服務(wù)停止情況下查看版本號信息

使用SQLcmd命令查看SQL server的版本，運(yùn)行sqlcmd.exe打開sqlcmd命令窗口，然后執(zhí)行1>sqlcmd select @@version，2>go

可以看出，@@version 版本號結(jié)果中還包含 OS 版本信息。

方法三：

通過日志獲取版本號，打開SQLServer默認(rèn)安裝目錄的Log文件下找到ErrorLog文件如圖（二），打開后可看到版本信息如（圖三），此方法適合SQLServer服務(wù)停止情況下查看版本號信息

圖（二）

圖（三）

或者打開SSMS 在SQLSERVER代理下通過日志查看器查看，如下圖（四）所示：

圖（四） 

方法四：

通過執(zhí)行select @@version獲取版本號信息，如下圖（五）所示,

圖（五）

方法五：

在 SQL Server Management Studio 中通過執(zhí)行[sys].[xp_msver]獲取版本號信息，如下圖（六）所示

圖（六）

方法六：

在 SQL Server Management Studio 中通過執(zhí)行下面腳本獲取版本號信息，結(jié)果如下圖（七）所示

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

圖（七）

方法七：

通過“SQL SERVER 安裝中心”獲取版本號信息

開始菜單，找到點(diǎn)擊后打開如圖（八）所示，點(diǎn)擊 “已安裝的SQLServer功能發(fā)現(xiàn)報(bào)告”后，顯示相關(guān)的版本信息，如圖（九）。

圖（八）

圖（九）

方法八：

今年11月18日, Microsoft OneScript 團(tuán)隊(duì)發(fā)布了最新一版在 SQL Server Management Studio 中運(yùn)行的腳本，可以幫助我們獲取更詳細(xì)的版本信息

推薦使用此法>>>>>>>>>>>>>>>

該腳本有以下幾點(diǎn)：

1. SQL Server 的主要版本、服務(wù)級別和版本類別 

2. 已安裝SP包、累計(jì)更新CU，歷史更新的QFE\ GDR  

3. 推薦當(dāng)前SP包可以安裝最新的CU，并給到相關(guān)資源地地址（這個(gè)很有幫助）

如Cumulative Update CU6 of SQL Server 2012 SP3.

4. 推薦可升級到其他產(chǎn)品,如:

    SQL Server 2014 Enterprise

    SQL Server 2014 Business Intelligence

    SQL Server 2016 Enterprise

    SQL Server 2016 Business Intelligence

5. 服務(wù)支持生命周期階段

有關(guān)此腳本的詳細(xì)信息，請到 TechNet 庫中的下載：下載點(diǎn)我

執(zhí)行后結(jié)果顯示如圖（十）：

圖（十）

 

方法九：

Microsoft OneScript 團(tuán)隊(duì)頁發(fā)布了通過Powershell獲取版本信息的腳本

Powershell腳本下載地址：點(diǎn)我

部分腳本如圖（十一）：

圖（十一）

執(zhí)行方法是：

1.確保SQL Server服務(wù)正常運(yùn)行

2.用administrator啟動(dòng)Windows PowerShell

3.執(zhí)行Execution Set-ExecutionPolicy unrestricted -Force

4: 輸入 DetermineVersionOfComponents.ps1 并回車

5: 輸入登錄數(shù)據(jù)庫的用戶名和密碼

結(jié)果顯示如下圖（十二）

圖（十二）

SQL Server版本更新的維護(hù)列表

請點(diǎn)擊下面鏈接下載，包括2005~2016所有版本的構(gòu)建版本號，KB號、發(fā)行日期、SP\CU等信息，如下圖（十三）所示：

下載鏈接：點(diǎn)我

  圖（十三）

SQL Server相關(guān)的所有版本的產(chǎn)品支持周期信息 

請點(diǎn)擊下面鏈接查詢：點(diǎn)我

 

---

小結(jié)，SQL Server版本維護(hù)是日常運(yùn)維中重要部分，我們支持的數(shù)據(jù)庫實(shí)例可能很多，因業(yè)務(wù)或歷史原因數(shù)據(jù)庫的版本也可能不一樣，搞清并建立每個(gè)數(shù)據(jù)庫版本檔案是件重要的事情，為后期的版本升級、遷移提供正確信息，降低過程中風(fēng)險(xiǎn)。