SQL Server – How to Find a List of Local MS SQL Servers

sql serversqlcmdvisual studio

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.

Best Answer

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://docs.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-express-localdb?view=sql-server-ver15

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