April 2012 - Posts

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 http://www.microsoft.com/technet/security/Bulletin/MS11-049.mspx 

Aaron's article: http://sqlblog.com/blogs/aaron_bertrand/archive/2011/06/14/security-updates-for-all-supported-versions-of-sql-server.aspx#comments

*** Updated script [ 24th April 2012 ] ***

--added CU1 (RTM) SQL 2012

--added CU6 (SP1) SQL Server 2008R2

--added CU4 (SP3) SQL Server 2008

Glenn Berry Reconmended I add a few links: 

Listing of SQL Server Builds: http://sqlserverbuilds.blogspot.co.nz/

The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 Service Pack 1 was released http://support.microsoft.com/kb/2567616

SQL Server 2008 builds that were released after SQL Server 2008 SP3 was released http://support.microsoft.com/kb/2629969

The SQL Server 2012 builds that were released after SQL Server 2012 was released http://support.microsoft.com/kb/2692828

 

;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 CU4 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.5775)'

                  WHEN Build = 5768 THEN 'SP3-CU2 installed, Consider apply latest CU2 for SP3(10.0.5775)'

                  WHEN Build = 5770 THEN 'SP3-CU3 installed, Consider apply latest CU2 for SP3(10.0.5775)'

                  WHEN Build = 5775 THEN '** SP3-CU4 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 CU6 for SP1'

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

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

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

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

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

                  WHEN Build = 2806 THEN 'SP1-CU5 installed, Consider apply latest CU6 for SP1 (10.50.2811)'

                  WHEN Build = 2811 THEN '** SP1-CU6 installed, Watch for newer CU releases for SP1'

            END  

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

      THEN

            CASE

                  WHEN Build < 1103 THEN 'CTP1 Installed, Consider moving to the RTM (11.00.2100) Release'

                  WHEN Build = 1440 THEN 'CTP3 Installed, Consider moving to the RTM (11.00.2100) Release'

                  WHEN Build = 1750 THEN 'RC0 Installed, Consider moving to the RTM (11.00.2100) Release'

                  WHEN Build = 1913 THEN 'RC1 Installed, Consider moving to the RTM (11.00.2100) Release'

                  WHEN Build = 2100 THEN 'RTM Installed, Consider apply latest CU1 for RTM (11.00.2316)'

                  WHEN Build = 2316 THEN 'Cumulative Update #1 fo the RTM of SQL Server.'

            END  

END  AS [Reconmendations]

FROM CTE_SQLEditions

Posted by NeilHambly | 1 comment(s)
Filed under: