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  - Usualy disclaimer of using script in your environments.. 

-- As per Aarons article - please ensure this is suitable for you environment

*** Updated script [ 1st March 2012 ] to add in SQL 2012 and CU4 | CU5 fro SQL Server 2008R2 Version ***

 

;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 CU5 for SP1'

                  WHEN Build = 2500 THEN 'SP1 Installed,also Consider apply latest CU5 for SP1 (10.50.2806)'

                  WHEN Build = 2769 THEN 'SP1-CU1 installed, Consider apply latest CU5 for SP1 (10.50.2806)'

                  WHEN Build = 2772 THEN 'SP1-CU2 installed, Consider apply latest CU5 for SP1 (10.50.2806)'

                  WHEN Build = 2789 THEN 'SP1-CU3 installed, Consider apply latest CU5 for SP1 (10.50.2806)'

                  WHEN Build = 2796 THEN 'SP1-CU4 installed, Consider apply latest CU5 for SP1 (10.50.2806)'

                  WHEN Build = 2806 THEN '** SP1-CU5 installed, Watch for newer CU releases for SP1'

            END  

      WHEN Major = 11 And Minor = 0      -- SQL 2012

      THEN

            CASE

                  WHEN Build < 1103 THEN 'Upgrade to latest Release Candidate (11.00.1750), Keep eye out for RC1 or RTM'

                  WHEN Build = 1440 THEN 'CTP3 Installed, Consider moving to latest Release Candidate RC0 (11.00.1750)'

                  WHEN Build = 1750 THEN 'RC0 installed, Keep eye out for RC1 or RTM'

                  WHEN Build > 1750 THEN '** You ahead of me ... I''ll try to catch up'

            END  

END  AS [Reconmendations]

FROM CTE_SQLEditions

 

 
Published 04 January 2012 11:16 by NeilHambly

Comments

04 January 2012 13:11 by NeilHambly

# re: SQL Server Builds

This was the previous posting on this I did

sqlblogcasts.com/.../posteditor.aspx