How to find a list of local MS SQL Servers?

Question

I looked at How can I determine installed SQL Server instances and their versions? already but CMD returns nothing:

C:\Windows\system32>sqlcmd -L

C:\Windows\system32>

But when using Visual Studio (v2019), I go to View > SQL Server Object Explorer, I see two SQL Server instances with system databases inside. One is called (LocalDB)\MSSQLLocalDB and the other (LocalDB)\ProjectsV13.

Both are using SQL Server 2016 (v13.0.4001). Using that I was able to connect through SSMS 2018 as well.

My question is: why aren't these instances showing up in the command line? And can I delete one of the server instance? Why are there two?

I have tried:

1. I checked my services and I there is only SQL Server VSS write. everything else is irrelevant to SQL. I looked up in SQL Server configuration manager but SQL Server services is empty there as well. I checked in C:\Program Files (x86)\Microsoft SQL Server\90\Shared and sqlbrowser.exe was not there also.

2. I just tried setting up inbound and outbound rules on TCP 1433 and UDP 1434 to allow connections. Failed again with nothing returned from cmd.

3. I turned on firewall logs and checked the logs after running the command a few times but nothing is being recorded (success or failure). I opened powershell and did test-netconnection to my ip and port 1433 and 1434 but the ping failed. Still nothing written to the firewall log files.

Answer 1

To discover instances of SQL localdb installed on your local machine you should use the SQLLocalDB utility. I found it installed at C:\Program Files\Microsoft SQL Server\150\Tools\Binn\SQLLocalDB.exe for SQL 2019. If you have the tool installed, you should be able to run the following from the command prompt to return a list of localdb instances:

sqllocaldb.exe i

To remove a localdb, type the following at the command prompt:

sqllocaldb.exe d nameofinstancetoremove

The following documentation helped me:

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-express-localdb?view=sql-server-ver15

https://learn.microsoft.com/en-us/sql/tools/sqllocaldb-utility?redirectedfrom=MSDN&view=sql-server-ver15

Answer 2

-- Powershell

Import-Module sqlserver 'computername'| Foreach-Object {Get-ChildItem -Path "SQLSERVER:\SQL\$_"}

Make sure you don't have linked server (LocalDB)\ProjectsV13 pointing to itself (LocalDB)\MSSQLLocalDB

Check any alias defined