How to check if SP1 for SQL Server 2008 R2 is already installed?

Question

I have trouble figuring out if the SP1 is already installed. I don't think I could check it from the Management Studio as its a different application. But the SQl server it self has no UI to check under "about". :)

Any ideas?

Trammel

Answer 1

There is not SP1 for SQL Server 2008 R2 just yet.....

But to check, you can inspect the productlevel server property:

SELECT  
  SERVERPROPERTY('productlevel') 

This will contain RTM for the original RTM version (as it is in my case with SQL Server 2008 R2 now), or it will contain info about the service pack installed.

I typically use this SQL query:

SELECT  
    SERVERPROPERTY('productversion') as 'Product Version', 
    SERVERPROPERTY('productlevel') as 'Patch Level',  
    SERVERPROPERTY('edition') as 'Product Edition',
    SERVERPROPERTY('buildclrversion') as 'CLR Version',
    SERVERPROPERTY('collation') as 'Default Collation',
    SERVERPROPERTY('instancename') as 'Instance',
    SERVERPROPERTY('lcid') as 'LCID',
    SERVERPROPERTY('servername') as 'Server Name'

This lists your server version, edition, service pack (if applicable) etc. - something like this:

Product Version  Patch Level  Product Edition             CLR Version  Default Collation     Instance  LCID  Server Name
10.50.1617.0       RTM        Developer Edition (64-bit)  v2.0.50727   Latin1_General_CI_AS   NULL     1033  *********

Update: this answer was correct when it was posted - July 2011.

By now, November 2012, there's SQL Server 2008 R2 Service Pack 2 available for download

Newsome

Hi, Many Thanks for your answer. I should have mentioned that I need it for the Expression Edition. There is already Sp2 for the express edition here: microsoft.com/download/en/details.aspx?id=20610 However once downloaded I can't install it. Do I have to select "New Sql Server stand-alone installation or ad features to an existing installation" ? The problem is, once I do that, in the window "Perform a new installation" or "Add features to an existing instance" It doesnt accept mu current instance. Why? – Trammel

It says Instance ID is required but is missing :( – Trammel

@Kave: there is a SP2 for SQL Server 2008 - but no SP yet for for 2008 R2 - those are two totally different versions ! – Newsome

oh my god. I should stop working. Its friday... :) You are right. I didn't see R2 and was going nuts. Thank you! – Trammel

Microsoft SQL Server 2008 R2 Service Pack 1 is available. – Hydrocephalus

@Neolisk: by now, there's even a SP2 for SQL Server 2008 R2 - but it wasn't back when I originally posted my answer... – Newsome

@marc_s: cmon, why did I download SP1 then - just now? Anyway, can you update your answer with this link? +1 – Hydrocephalus

Answer 2

Note that there are two versions of Service Pack 1 - the CTP that was released in April, and the final release which should be released any day now. Both of these versions will return "SP1" for ProductLevel, and both will say "Microsoft SQL Server 2008 R2 (SP1) ..." for @@VERSION. The difference will be that @@VERSION for the CTP will next say 10.50.2425 and reflect a date of April 6, 2011; the final release will have a higher build number and reflect a later date (I will try to remember to come back and update this post when the SP has been released for good).

Sorry for joining the discussion late, but for the benefit of future readers, I wanted to make it clear that just relying on ProductLevel saying "SP1" is not the most foolproof way to ensure that you are at the final version of Service Pack 1.

And finally, don't feel bad about getting 2008 & 2008 R2 mixed up. This was a brain-dead Microsoft naming blunder along the lines of TIMESTAMP, DATETIME2 and DACPAC ... and based on the number of people who continue to try to apply 2008 updates to R2 and vice-versa, and the lack of adoption due at least in part to the rather accurate impression that R2 is quite likely the most expensive service pack ever, I hope they've learned how foolish it was.

Elisabeth

Would the final version of SP1 for 2008 R2 then be 10.50.2500.0 - Jun 17 2011 ? – Cosmopolite

Answer 3

To check the SQL SERVER Information of Installed Version use this script:

   SELECT 
              SERVERPROPERTY('MachineName') as Host,
              SERVERPROPERTY('InstanceName') as Instance,
              SERVERPROPERTY('Edition') as Edition, /*shows 32 bit or 64 bit*/
              SERVERPROPERTY('ProductLevel') as ProductLevel, /* RTM or SP1 etc*/
              Case SERVERPROPERTY('IsClustered') when 1 then 'CLUSTERED' else
              'STANDALONE' end as ServerType,
              @@VERSION as VersionNumber

Product level will determine if you have SP1, SP2 Installed or not.

Chemmy