verify version of MS sql

bankadmin
bankadmin used Ask the Experts™
on
I have several servers running MS SQL Server 2008. How do I verify what patch level its running? If I go into add remove programs there is no version listed for Microsoft SQL Server 2008 but there is a version for all other things listed as MS SQL Server 2008 such as Browser, Native Client, extt. all those versions are the same. Do those versions represent what version Im running? If I go in to Managment studio and go to help it gives me versions for several things but not SQL server
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Administrator
Top Expert 2005
Commented:
SELECT @@VERSION
Steve WalesSenior Database Administrator

Commented:
And you can then look up the number returned against this list to work out what the SP or Cum Patch level is: http://sqlserverversions.blogspot.com/

(It's not MS official but it's been there for years and is still maintained at the moment as far as I know)

Author

Commented:
Where do I select Version from?
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Steve WalesSenior Database Administrator
Commented:
There is no from.  Just run "Select @@version"

SQL Server doesn't always require a FROM when querying certain items about the server or database.

(For example, you can also do:  SELECT getdate()   - that will return the date)
Olaf DoschkeSoftware Developer

Commented:
It's the @@Version system variable, that is holding the version info. You wouldn't need SELECT, but it is the simplest way to return a query result. If you like you could also create a scalar function and return @@Version, but there is no point in doing that, if it's much simpler to do SELECT @@Version.

The T-SQL help topic on @@Server also refers to SERVERPROPERTY, see
https://msdn.microsoft.com/en-us/library/ms174396.aspx 
You may use that to get more detailed single infos. Look out some properties only exist from SQL 2012 onwards.

And there are more methods, eg see https://support.microsoft.com/en-us/kb/321185

If you see suspicious database behaviour or miss something, check out the compatibility level of a database by querying

SELECT name, compatibility_level FROM sys.databases

Open in new window


Also see https://msdn.microsoft.com/en-us/library/bb510680.aspx

Author

Commented:
I logged into managment studio and executed the select @@VERSION command and it just sits on Debugging Query in a yellow bar in the window, in the same yellow bar it states servername (10.0 SP3).. Is that the version? Thanks for helping this SQL novice thru this.
Steve WalesSenior Database Administrator

Commented:
How did you Execute it ?  Debugging query sounds weird ?

You should just be able to open Management Studio.  Click New Query.  In the query window "select @@version" then Control-E to Execute.

The output that appears on one server of mine (it's an old one, don't laugh):

Microsoft SQL Server 2005 - 9.00.5324.00 (X64)   Aug 24 2012 18:28:47   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Looking up that build number (9.00.5324) on that site I linked earlier - I found it wasn't listed!

Had to do some digging but found this:
http://www.sqlservercentral.com/articles/Administration/2960/

Which tells me that it's "MS12-070: Description of the security update for SQL Server 2005 Service Pack 4 QFE: October 9, 2012 "

You should be able to follow a similar procedure to get output and decode it.
Olaf DoschkeSoftware Developer

Commented:
Seems you clicked on the green play symbol, that's starting a debug process. You need to click the Exclamation point or "Execute" or use CTRL+E to execute.

Just wait for the tooltip texts before you click on icons, as intuitive as they may look. And if something unexpected happend, well, it was not inituitive.

As you mention 10.0 SP3, this reminds me: the SQL Server version roughly is already shown, when you connect to a server in the object explorer root node in paranthesis after the server name,

Bye, Olaf.
Jose TorresCertified Database Administrator

Commented:
If you have ssms
1. Connect to the server
2. On the server it will tell you the build (refer to image)
SSMS Build3. Go to this site and it will tell you which SP you are on based on the build
http://sqlserverbuilds.blogspot.com/

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial