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?

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