How can I determine installed SQL Server instances and their versions

Question

I m trying to determine what instances of sql server sql express I have installed  either manually or programmatically  but all of the examples are telling me to run a SQL query to determine this which assumes I m already connected to a particular instance

User · Answer

All of the instances installed should show up in the Services Snap-In in the Microsoft Management Console   To get the instance names  go to Start   Run   type Services msc and look for all entries with  Sql Server  Instance Name

User · Answer

At a command line   SQLCMD -L   or   OSQL -L    Note  must be a capital L   This will list all the sql servers installed on your network   There are configuration options you can set to prevent a SQL Server from showing in the list   To do this     At command line   svrnetcn   In the enabled protocols list  select  TCP IP   then click properties   There is a check box for  Hide server

User · Answer

I had this same issue when I was assessing 100  servers  I had a script written in C  to browse the service names consist of SQL  When instances installed on the server  SQL Server adds a service for each instance with service name  It may vary for different versions like 2000 to 2008 but for sure there is a service with instance name    I take the service name and obtain instance name from the service name  Here is the sample code used with WMI Query Result   if  ServiceData DisplayName     MSSQLSERVER     ServiceData DisplayName     SQL Server  MSSQLSERVER                                  InstanceData Name    DEFAULT                   InstanceData ConnectionName   CurrentMachine Name                  CurrentMachine ListOfInstances Add InstanceData                             else                 if  ServiceData DisplayName Contains  SQL Server        true                                        InstanceData Name   ServiceData DisplayName Substring                                              ServiceData DisplayName IndexOf        1                                              ServiceData DisplayName IndexOf      - ServiceData DisplayName IndexOf      - 1                                                                InstanceData ConnectionName   CurrentMachine Name          InstanceData Name                      CurrentMachine ListOfInstances Add InstanceData                                     else                     if  ServiceData DisplayName Contains  MSSQL       true                                                InstanceData Name   ServiceData DisplayName Substring                                                  ServiceData DisplayName IndexOf        1                                                  ServiceData DisplayName Length - ServiceData DisplayName IndexOf      - 1                                                                         InstanceData ConnectionName   CurrentMachine Name          InstanceData Name                          CurrentMachine ListOfInstances Add InstanceData

User · Answer

-- T-SQL Query to find list of Instances Installed on a machine  DECLARE  GetInstances TABLE   Value nvarchar 100    InstanceNames nvarchar 100    Data nvarchar 100    Insert into  GetInstances EXECUTE xp regread    rootkey    HKEY LOCAL MACHINE      key    SOFTWARE Microsoft Microsoft SQL Server      value name    InstalledInstances   Select InstanceNames from  GetInstances

User · Answer

I know this thread is a bit old  but I came across this thread before I found the answer I was looking for and thought I d share   If you are using SQLExpress  or localdb  there is a simpler way to find your instance names  At a command line type    gt  sqllocaldb i   This will list the instance names you have installed locally   So your full server name should include  localdb   in front of the instance name to connect   Also  sqllocaldb allows you to create new instances or delete them as well as configure them   See  SqlLocalDB Utility

User · Answer

I just installed Sql server 2008  but i was unable to connect to any database instances  The commands  G Mastros posted listed no active instances   So i looked in services and found that the SQL server agent was disabled  I fixed it by setting it to automatic and then starting it

User · Answer

Here is a simple method  go to  Start then Programs then  Microsoft SQL Server 2005 then Configuration Tools then SQL Server Configuration Manager then SQL Server 2005 Network Configuration then Here you can locate all the instance installed onto your machine

User · Answer

If your within SSMS you might find it easier to use   SELECT   Version

User · Answer

I know its an old post but I found a nice solution with PoweShell where you can find SQL instances installed on local or a remote machine including the version and also be extend get other properties     MachineName             Default local computer Replace   with server name for a remote computer   reg    Microsoft Win32 RegistryKey   OpenRemoteBaseKey    LocalMachine      MachineName   regKey   reg OpenSubKey  SOFTWARE  Microsoft  Microsoft SQL Server  Instance Names  SQL     values    regkey GetValueNames    values   ForEach-Object   value         inst    regKey GetValue  value                   path    SOFTWARE  Microsoft  Microsoft SQL Server     inst    MSSQLServer     CurrentVersion                  write-host  path                  version    reg OpenSubKey  path  GetValue  CurrentVersion                              write-host  Instance   value                write-host   Version   version

User · Answer

From Windows command-line  type   SC   server name query   find  I  SQL Server      Where  server name  is the name of any remote server on which you wish to display the SQL instances   This requires enough permissions of course

User · Answer

The commands OSQL -L and SQLCMD -L will show you all instances on the network   If you want to have a list of all instances on the server and doesn t feel like doing scripting or programming  do this    Start Windows Task Manager Tick the checkbox  Show processes from all users  or equivalent Sort the processes by  Image Name  Locate all sqlsrvr exe images   The instances should be listed in the  User Name  column as MSSQL INSTANCE NAME   And I went from thinking the poor server was running 63 instances to realizing it was running three  out of which one was behaving like a total bully with the CPU load

User · Answer

You could query this registry value to get the SQL version directly   HKEY LOCAL MACHINE SOFTWARE Microsoft Microsoft SQL Server 90 Tools ClientSetup CurrentVersion   Alternatively  you can query your instance name and then use sqlcmd with your instance name that you would like    To see your instance name   HKEY LOCAL MACHINE SOFTWARE Microsoft Microsoft SQL Server Instance Names   Then execute this   SELECT SERVERPROPERTY  productversion    SERVERPROPERTY   productlevel    SERVERPROPERTY   edition     If you are using C   you can use this code to get the registry information

User · Answer

SQL Server permits applications to find SQL Server instances within the current network  The SqlDataSourceEnumerator class exposes this information to the application developer  providing a DataTable containing information about all the visible servers  This returned table contains a list of server instances available on the network that matches the list provided when a user attempts to create a new connection  and expands the drop-down list containing all the available servers on the Connection Properties dialog box  The results displayed are not always complete  In order to retrieve the table containing information about the available SQL Server instances  you must first retrieve an enumerator  using the shared static Instance property   using System Data Sql   class Program     static void Main              Retrieve the enumerator instance and then the data      SqlDataSourceEnumerator instance         SqlDataSourceEnumerator Instance      System Data DataTable table   instance GetDataSources            Display the contents of the table      DisplayData table        Console WriteLine  Press any key to continue         Console ReadKey           private static void DisplayData System Data DataTable table          foreach  System Data DataRow row in table Rows              foreach  System Data DataColumn col in table Columns                  Console WriteLine   0     1    col ColumnName  row col                  Console WriteLine                                                from msdn http   msdn microsoft com en-us library a6t1z9x2 v vs 80  aspx

User · Answer

This query should get you the server name and instance name     SELECT   SERVERNAME    SERVICENAME

User · Answer

Will get the instances of SQL server  reg query  HKLM Software Microsoft Microsoft SQL Server Instance Names SQL   or Use  SQLCMD -L

User · Answer

I had the same problem  The  osql -L  command displayed only a list of servers but without instance names  only the instance of my local SQL Sever was displayed   With Wireshark  sqlbrowser exe  which can by found in the shared folder of your SQL installation  I found a solution for my problem   The local instance is resolved by registry entry  The remote instances are resolved by UDP broadcast  port 1434  and SMB  Use  sqlbrowser exe -c  to list the requests   My configuration uses 1 physical and 3 virtual network adapters   If I used the  osql -L  command the sqlbrowser displayed a request from one of the virtual adaptors  which is in another network segment   instead of the physical one  osql selects the adpater by its metric  You can see the metric with command  route print   For my configuration the routing table showed a lower metric for teh virtual adapter then for the physical  So I changed the interface metric in the network properties by deselecting automatic metric in the advanced network settings  osql now uses the physical adapter

User · Answer

SQL Server Browser Service http   msdn microsoft com en-us library ms181087 aspx

User · Answer

One more option would be to run SQLSERVER discovery report  go to installation media of sqlserver and double click setup exe     and in the next screen go to tools and click discovery report as shown below    This will show you all the instances present along with entire features  below is a snapshot on my pc

User · Answer

If you just want to see what s installed on the machine you re currently logged in to  I think the most straightforward manual process is to just open the SQL Server Configuration Manager  from the Start menu   which displays all the SQL Services  and only SQL services  on that hardware  running or not    This assumes SQL Server 2005  or greater  dotnetengineer s recommendation to use the Services Management Console will show you all services  and should always be available  if you re running earlier versions of SQL Server  for example    If you re looking for a broader discovery process  however  you might consider third party tools such as SQLRecon and SQLPing  which will scan your network and build a report of all SQL Service instances found on any server to which they have access   It s been a while since I ve used tools like this  but I was surprised at what they found  namely  a handful of instances that I didn t know existed    YMMV   You might Google for details  but I believe this page has the relevant downloads  http   www sqlsecurity com Tools FreeTools tabid 65 Default aspx

User · Answer

If you are interested in determining this in a script  you can try the following   sc   server name query   grep MSSQL   Note  grep is part of gnuwin32 tools

[sql-server] How can I determine installed SQL Server instances and their versions?

Examples related to sql-server

Examples related to sql-server-express