Today morning when I installed 2016 CTE3 over 2012 as a separate instance and tried connecting but failed with errors then thought of do some research work of ways to figure out what happened to me if forgot to remember instance names given. it happens always with me… then this handy document can help many others having the same issue of new DBA who don’t have much information of local database instances .
Different ways to figure out the installed SQL Server default or named instances.
- From RUN CMD type SQLCMD -L, you will see the instance names in local machine.
- When you have multiple SQL instances installed or various versions of SQL Server was installed go to the latest or more upgraded versions Configuration Manager and click on SQL Server Services under Configuration Manager(local) main tab. And add local if you see MSSQLSERVER then you are using default instance name if not Named instance name
2a. When default instance you can try connecting using local <host name>
2b. When named instance you connect using your HOSTNAME\<service name>
NOTE : Service name would be from CMGR.
- Right click on SQL Server(service-name) from configmgr and from properties tab go to service tab and then see the end to the binary path you will find the actual service name.
- Note :You can connect using hostname\<namedservicename>.
- If its a default service name you can connect my using “.” or saying (local).
- And another easiest way is to use the <browse for more … > in SSMS at Server Name drop down this might little longer depending upon AD forest /network and no of instances installed.
Note : if you have connected to many servers from your local SSMS you might also see the historic connections made , but you should be able to recognize the rite one which is having the local machine name tagged in.
Please help me and help your self if any more other ways you know. off course sharing knowledge enrich your capability of thinking and remembering try it.