Detect what version of SQL Server Express engine is available before connection?

Question

I would like to detect what version of SQL Server Express engine is available so that I can connect either to the (localdb)\v.11 (SQL Server 2012 per https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2012/hh510202(v=sql.110)?redirectedfrom=MSDN#Anchor_1) or (localdb)\MSSQLLocalDB (SQL Server 2014 and up per https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-express-localdb?view=sql-server-ver15&redirectedfrom=MSDN&viewFallbackFrom=sql-server-2014#Anchor_1) instance name when attempting to attach a file in the connection string using the AttachDBFileName= mechanism.

I would most likely want to do it from Powershell in some manner, but whatever method is reliable, I can use. I do know that sometimes a LocalDB connection can be a bit slow as it attaches the file and starts up on demand, so I have in the past been pretty lenient on the connect timeout for these LocalDB connections compared to the real SQL Server connections, so I would prefer not trying to connect and waiting for timeout, since I think I've already got an exaggerated timeout just for normal successful connection.

• Charlieface: Side note: what's the issue with AttachDbFilename and Bad Habits : Using AttachDBFileName
• Cade Roux: @Charlieface yes, these are database files containing test scenarios inside zip files that are maintained by other applications that test designers keep under source control and my build process needs to load the tests from many of those files into my testing database to analyze them run them, etc. These aren't my application database or anything, they are a temporary data source I get from source control, unzip, attach, extract the data and detach.
• Cade Roux: The reason for my need is that there are a number of Team City build agents and some have SQL Server 2012 Express and some have SQL 2016 Express, but they are all in the same pool, and I don't want to have to manage a separate environment variable for every build agent - I just want to autodetect it in my script to reduce maintenance.

Answer 1

You can check the Windows registry for installed versions.

For example, in Powershell you can use this to find all installed versions (note there may be multiple versions installed)

(Get-ChildItem
 -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server Local DB\Installed Versions")
.PSChildName

You would want to check that 12.0 or up was in the results.