18

I've seen this link, but with no help: How can I determine installed SQL Server instances and their versions?

So here is my question: enter image description here

I want to see what versions of sql are installed on my computer.

If I'm running a query and typing : 

select @@version

It tells me the running thread version. (which I don't want)

I want to see all of the versions - if installed!

Example: a computer can have sql 2000, 2005, 2008 ,2008R2

I've attached a print screen for my sql server folders.

I know that

80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008

How ever, it is not a rule.

Since 2008 creates both 80, 90.

It seems that the only way to find what versions are installed (besides regedit) is by the Sql Server Configuration manager.

enter image description here

Questions:

1) Is it possible by file system only, to detect what versions I have installed?

2) Is there any other way (besides registry), to see what SQL versions installed?

3) I read that mssql.1 is for the engine. What about MSSQL10.SQLEXPRESS? Why it doesn't have mssql.4 number? MSDN says that it's a sequential ID of installed components.

Community
  • 1
  • 1
Royi Namir
  • 144,742
  • 138
  • 468
  • 792

4 Answers4

12

SQL Server 2008 Discovery Report

How can i tell what SQL Server features and version do I have installed? This question has been asked a lot recently. Starting in SQL Server 2008, we added a SQL Server discovery report as an option on the Tools page on the Installation Center. When you click on the link below, SQL Server will be started to discover the SQL Server features.

The SQL Server Discovery Report is saved to %ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\

Options:

You can also generate the Discovery report through the command line. Run “Setup.exe /Action=RunDiscovery” from a command prompt If you add “/q” to the command line above no UI will be shown, but the report will still be created in %ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\20091112_082147.

Reference:

Arion
  • 31,011
  • 10
  • 70
  • 88
  • Why do it the hard way. When you can do it with an already implemented feature:) – Arion Jan 30 '12 at 13:04
  • @Arion, The real question is why did Microsoft not make it usably-obvious to find out the version in the first place. – Pacerier Aug 25 '15 at 06:16
9

Usually,

  • SQL Server 2005 uses the following directory naming scheme: MSSQL.1, MSSQL.2, ...,
  • SQL Server 2008 uses MSSQL10.<INSTANCENAME>, and
  • SQL Server 2008R2 uses MSSQL10_50.<INSTANCENAME>.

However, since the data directory can be changed during installation (at least for newer SQL Server versions), this is not a reliable way to detect SQL Server instances.

Heinzi
  • 167,459
  • 57
  • 363
  • 519
0

Hmmm... If you want to know what versions do you have using only "the filesystem", the closest thing I know is opening the errorlog (~MSSQL\LOG\ERRORLOG) and whenever you start a connection, the sql server instance version is logged. You can search for "Microsoft SQL Server" and you will see things as "Microsoft SQL Server 2008 (SP1)" or Microsoft SQL Server 2003" and so on

Gaspa79
  • 5,488
  • 4
  • 40
  • 63
0

If you are using .NET, you can use the SmoApplication.EnumAvailableSqlServers method from SMO to enumerate all local SQL servers and get their version info.

Here is an SO example.

Community
  • 1
  • 1
Jakob Christensen
  • 14,826
  • 2
  • 51
  • 81