SQL Server Builds
If you like me - Knowing which Build you should have deployed when we look @ the myriad of Service Packs & Cumulative Updates possible, it can quickly become confusing
What actions should I do to "PATCH" a server to the appropriate build, So here is a quick script that can easily be updated as new builds are made available, and easily customized for your own environments
-- This Script only reports on your current version and recommends your course of action
-- As per Aarons article - please ensure this is suitable for you environment
;With CTE_SQLEditions([Major],[Minor],[Build],[BuildMinor])
AS
(
select
parsename(convert(varchar,serverproperty ('productversion')),4) As Major,
parsename(convert(varchar,serverproperty ('productversion')),3) As Minor,
parsename(convert(varchar,serverproperty ('productversion')),2) As Build,
parsename(convert(varchar,serverproperty ('productversion')),1) As Buildminor
)
Select @@SERVERNAME, *
,CASE
WHEN Major = 9 -- SQL 2005
THEN
CASE
WHEN Build < 5000 THEN 'Upgrade to Service Pack 4 (9.0.5000), also consider applying the latest CU3 for SP4'
WHEN Build = 5000 THEN 'SP4 Installed, Consider apply latest CU3 for SP4(9.0.5266)'
WHEN Build = 5254 THEN 'SP4-CU1 installed, Consider apply latest CU3 for SP4(9.0.5266)'
WHEN Build = 5259 THEN 'SP4-CU2 installed, Consider apply latest CU3 for SP4(9.0.5266)'
WHEN Build = 5266 THEN '** SP4-CU3 installed, Watch for newer CU releases for SP4'
END
WHEN Major = 10 And Minor = 0 -- SQL 2008
THEN
CASE
WHEN Build < 5416 THEN 'Upgrade to Service Pack 3 (10.0.5416), also consider applying the latest CU2 for SP3'
WHEN Build = 5416 THEN 'SP3 Installed, Consider apply latest CU2 for SP3(10.0.5768)'
WHEN Build = 5776 THEN 'SP3-CU1 installed, Consider apply latest CU2 for SP3(10.0.5768)'
WHEN Build = 5768 THEN '** SP3-CU2 installed, Watch for newer CU releases for SP3'
END
WHEN Major = 10 And Minor = 50 -- SQL 2008 R2
THEN
CASE
WHEN Build < 2500 THEN 'Upgrade to Service Pack 1 (10.50.2500), also consider applying the latest CU3 for SP1'
WHEN Build = 2500 THEN 'SP1 Installed, Consider apply latest CU3 for SP1(10.50.2789)'
WHEN Build = 2769 THEN 'SP1-CU1 installed, Consider apply latest CU3 for SP1(10.50.2789)'
WHEN Build = 2772 THEN 'SP1-CU2 installed, Consider apply latest CU3 for SP1(10.50.2789)'
WHEN Build = 2789 THEN '** SP1-CU3 installed, Watch for newer CU releases for SP1'
END
END
FROM CTE_SQLEditions