解决方法
以相反的顺序回答
我的版本是什么？
第一个是@@version.关于这一点的好处是它还带回了操作系统级别的信息.它的挑战在于它是一个很大的文本转储,你必须解析它以找到相关的位.
例如
SELECT @@version; /* Microsoft sql Server 2014 - 12.0.4416.0 (X64) Jun 11 2015 19:18:41 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor) */
我的偏好是使用ServerProperties,因为我可以单独识别元素.
SELECT
SERVERPROPERTY('productversion') AS ProductVersion,SERVERPROPERTY ('productlevel') AS ProductLevel,SERVERPROPERTY ('edition') AS Edition;
/*
ProductVersion ProductLevel Edition
12.0.4416.0 SP1 Developer Edition (64-bit)
*/
是否有检查补丁的本机功能？
没什么我知道的.如果主机操作系统运行Windows Update服务且某人指定了“包含其他MS产品的修补程序”,则可能会为sql Server安装补丁程序.
但是,如果您是专业人士,您可能对等待在WSUS中列出的补丁感兴趣,或者您想验证补丁是否适用于您的环境.这将涉及阅读知识库(KB)文章和测试.
如何判断我的sql Server实例是否已修补？
由于缺乏产品中的任何内容,我一直在引用sqlserverbuilds.blogspot.com.但是还有很多其他网站试图提供相同的补丁列表,例如：
> http://sqlserverupdates.com/
> http://blogs.sqlsentry.com/category/sql-server-builds/
我从该博客获取了数据并将其转换为View,dbo.PatchLevel.该视图公开了所有补丁.此视图被截断为仅涵盖2016年和2014年,否则我会对答案进行字符限制.
CREATE VIEW dbo.PatchLevel AS -- data from http://sqlserverbuilds.blogspot.com WITH SRC(Build,[File version],[KB / Description],[Release Date],SimpleVersion) AS ( SELECT CASE LEN(D.Build) - LEN(REPLACE(D.Build,'.','')) WHEN 3 THEN REPLACE(D.Build,'.00.','.0.') WHEN 2 THEN REPLACE(D.Build,'.0.') + '.0' END AS Build,D.FileVersion,D.KB,CAST(REPLACE(D.ReleaseDate,' *new','') AS date) AS ReleaseDate,CAST(LEFT(D.Build,4) AS decimal(4,2)) FROM ( VALUES ('13.00.500.53','2016.130.500.53','Microsoft sql Server 2016 Community Technology Preview 2.3 (CTP2.3)','August 28,2015 *new'),('13.00.407.1','2016.130.407.1','Microsoft sql Server 2016 Community Technology Preview 2.2 (CTP2.2)','July 23,2015'),('13.00.400.91','2016.130.400.91','Microsoft sql Server 2016 Community Technology Preview 2.2 (CTP2.2) [withdrawn]','July 22,('13.00.300.44','2016.130.300.444','Microsoft sql Server 2016 Community Technology Preview 2.1 (CTP2.1)','June 24,('13.00.200.172','2016.130.200.172','Microsoft sql Server 2016 Community Technology Preview 2 (CTP2)','May 27,('12.00.4427','2014.120.4427.24','3094221 Cumulative update package 3 (CU3) for sql Server 2014 Service Pack 1','October 21,('12.00.4422','2014.120.4422.0','3075950 Cumulative update package 2 (CU2) for sql Server 2014 Service Pack 1','August 17,('12.00.4416','2014.120.4416.0','3067839 Cumulative update package 1 (CU1) for sql Server 2014 Service Pack 1','June 22,('12.00.4213','2014.120.4213.0','MS15-058: Description of the nonsecurity update for sql Server 2014 Service Pack 1 GDR: July 14,2015','July 14,('12.00.4100','2014.120.4100.1','sql Server 2014 Service Pack 1 (SP1)','May 14,('12.00.4050','2014.120.4050.0','sql Server 2014 Service Pack 1 (SP1) [withdrawn]','April 15,('12.00.2556','2014.120.2556.4','3094220 Cumulative update package 10 (CU10) for sql Server 2014','October 20,('12.00.2553','2014.120.2553.0','3075949 Cumulative update package 9 (CU9) for sql Server 2014',('12.00.2548','2014.120.2548.0','MS15-058: Description of the security update for sql Server 2014 QFE: July 14,('12.00.2546','2014.120.2546.0','3067836 Cumulative update package 8 (CU8) for sql Server 2014',('12.00.2506','2014.120.2506.0','3063054 Update enables Premium Storage support for Data files on Azure Storage and resolves backup failures','May 19,('12.00.2505','2014.120.2505.0','3052167 FIX: Error 1205 when you execute parallel query that contains outer join operators in sql Server 2014',('12.00.2504','2014.120.2504.0','2999809 FIX: Poor performance when a query contains table joins in sql Server 2014','May 5,'3058512 FIX: Unpivot Transformation task changes null to zero or empty strings in SSIS 2014',('12.00.2495','2014.120.2495.0','3046038 Cumulative update package 7 (CU7) for sql Server 2014','April 23,('12.00.2488','2014.120.2488.0','3048751 FIX: Deadlock cannot be resolved automatically when you run a SELECT query that can result in a parallel batch-mode scan','April 1,('12.00.2485','2014.120.2485.0','3043788 An on-demand hotfix update package is available for sql Server 2014','March 16,('12.00.2480','2014.120.2480.0','3031047 Cumulative update package 6 (CU6) for sql Server 2014','February 16,('12.00.2474','2014.120.2474.0','3034679 FIX: AlwaysOn availability groups are reported as NOT SYNCHRONIZING','May 15,('12.00.2472','2014.120.2472.0','3032087 FIX: Cannot show requested dialog after you connect to the latest sql Database Update V12 (preview) with sql Server 2014','January 28,('12.00.2464','2014.120.2464.0','3024815 Large query compilation waits on RESOURCE_SEMAPHORE_QUERY_COMPILE in sql Server 2014','January 5,('12.00.2456','2014.120.2456.0','3011055 Cumulative update package 5 (CU5) for sql Server 2014','December 18,2014'),('12.00.2436','2014.120.2436.0','3014867 FIX: "Remote hardening failure" exception cannot be caught and a potential data loss when you use sql Server 2014','November 27,('12.00.2430','2014.120.2430.0','2999197 Cumulative update package 4 (CU4) for sql Server 2014',('12.00.2423','2014.120.2423.0','3007050 FIX: RTDATA_LIST waits when you run natively stored procedures that encounter expected failures in sql Server 2014','October 22,('12.00.2405','2014.120.2405.0','September 25,('12.00.2402','2014.120.2402.0','2984923 Cumulative update package 3 (CU3) for sql Server 2014','August 18,('12.00.2381','2014.120.2381.0','2977316 MS14-044: Description of the security update for sql Server 2014 (QFE)','August 12,('12.00.2370','2014.120.2370.0','2967546 Cumulative update package 2 (CU2) for sql Server 2014','June 27,('12.00.2342','2014.120.2342.0','2931693 Cumulative update package 1 (CU1) for sql Server 2014','April 21,('12.00.2269','2014.120.2269.0','3045324 MS15-058: Description of the security update for sql Server 2014 GDR: July 14,('12.00.2254','2014.120.2254.0','2977315 MS14-044: Description of the security update for sql Server 2014 (GDR)',('12.00.2000','2014.120.2000.8','sql Server 2014 RTM',('12.00.1524','2014.120.1524.0','Microsoft sql Server 2014 Community Technology Preview 2 (CTP2)','October 15,2013') ) D(Build,FileVersion,KB,ReleaseDate) ) SELECT SRC.Build,SRC.[File version],SRC.[KB / Description],SRC.[Release Date],SRC.SimpleVersion FROM SRC; GO
我有第二个视图,是嵌套视图 – 坏我,dbo.MyPatchLevel,它使用上面的版本数据与我当前的实例进行比较并生成输出,让我知道它有多糟糕.
CREATE VIEW dbo.MyPatchLevel
AS
WITH MostRecentBuild AS
(
SELECT
SRC.Build,SRC.SimpleVersion,SRC.[KB / Description]
FROM
dbo.PatchLevel AS SRC
WHERE
SRC.[Release Date] =
(
SELECT
MAX(SRCI.[Release Date])
FROM
dbo.PatchLevel AS SRCI
WHERE
SRCI.SimpleVersion = SRC.SimpleVersion
)
AND
SRC.Build =
(
SELECT
MAX(SRCI.Build)
FROM
dbo.PatchLevel AS SRCI
WHERE
SRCI.SimpleVersion = SRC.SimpleVersion
AND SRCI.[Release Date] = SRC.[Release Date]
)
),MyVersion AS
(
SELECT
SRC.Build,MRB.[KB / Description],D.ProductVersion,D.ProductLevel,D.Edition,D.Version,MRB.Build AS MostRecentBuild,MRB.[Release Date] AS MostRecentReleaseDate
FROM
dbo.PatchLevel AS SRC
INNER JOIN
(
SELECT
SERVERPROPERTY('productversion') AS ProductVersion,SERVERPROPERTY ('edition') AS Edition,@@VERSION AS Version
)D
ON D.ProductVersion = SRC.Build
INNER JOIN
MostRecentBuild AS MRB
ON MRB.SimpleVersion = SRC.SimpleVersion
)
SELECT
MV.ProductVersion AS CurrentVersion,MV.MostRecentBuild,MV.[Release Date],MV.MostRecentReleaseDate,DATEDIFF(DAY,MV.MostRecentReleaseDate) AS DaysSincePatchAvailable,MV.[File version],MV.ProductLevel,MV.[KB / Description] AS [Most Recent KB / Description],MV.Edition,MV.Version,MV.SimpleVersion,CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS sysname) AS Server,CAST(SERVERPROPERTY('InstanceName') AS sysname) AS Instance,CAST(SERVERPROPERTY('ServerName') AS sysname) AS ServerName
FROM
MyVersion AS MV;
GO
我会使用像这样的查询
SELECT
MPL.CurrentVersion,MPL.MostRecentBuild,MPL.[Release Date],MPL.MostRecentReleaseDate,MPL.DaysSincePatchAvailable,MPL.[Most Recent KB / Description]
FROM
dbo.MyPatchLevel AS MPL;
结果
CurrentVersion MostRecentBuild Release Date MostRecentReleaseDate DaysSincePatchAvailable Most Recent KB / Description 12.0.4416.0 12.0.4422.0 2015-06-22 2015-08-17 56 3075950 Cumulative update package 2 (CU2) for sql Server 2014 Service Pack 1
有了这些查询结果,我知道我是否已修补到最新版本,自修补以来已经修补了多长时间以及在当前补丁状态下读取KB的内容.
脚本也在github托管
