How can I specify to use SQL Server LocalDb 2014 rather than SQL Server LocalDb 2016 in the connection string?

Question

Our application uses SQL Server LocalDb 2014 as the database engine. The connection string we use is

"Data Source=(localdb)\MSSQLLOCALDB;Initial Catalog=OurDatabase;MultipleActiveResultSets=True;Integrated Security=True;AttachDBFilename=|DataDirectory|OurDatabase.mdf"

Now, on just one of our computers, it has VS 2015SP3 and the latest version of the SQL Server objects installed, our application starts using SQL Server LocalDb 2016. This is undesirable as we exchange back-ups of the database files regularly between computers and now the back-ups that are made in the LocalDb 2016 format cannot be read on computers that do not have LocalDb 2016.

The problem is that the connection string does not specify which version of LocalDb should be used. It there a way to force LocalDb 2014 (or 2016, if we decide to upgrade?)

Dabblernl · Accepted Answer · 2016-10-15T06:05:22.893

Well, seeing that apart from Erik's answer no solutions have been provided, we must assume that indeed you cannot specify which flavour of SQL Server LocalDb you wish to use when using "Data Source=(localdb)\mssqllocaldb" in a connection string.

A drawback of Erik's solution is that it does not play nice with other applications that may use the default instance of LocalDb (MSSQLLocalDb). I found a different approach in using a so called named instance: an instance of LocalDb private to your application. While defining a named instance you can specify the version of LocalDb you want to use: 12.0 for LocaldDb 2014, 13.0 for Localdb 2016.

There are two ways to create a named instance:

Using the sqllocaldb commandline tool:

SqlLocalDB.exe create "MyNamedInstance" 12.0 -s

The -s parameter starts the instance immediately.

Specifying the named instance in app.config:

For this add to the <configSections> tag:

<section name="system.data.localdb"
         type="System.Data.LocalDBConfigurationSection,System.Data,Version=4.0.0.0,Culture=neutral,PublicKeyToken=b77a5c561934e089"/>

Then add a new tag:

<system.data.localdb>
    <localdbinstances>
      <add name="MyNamedInstance" version="12.0" />
    </localdbinstances>
</system.data.localdb>

You can now specify the named instance in the connection string thus:

"Data Source=(localdb)\mynamedinstance"

score 1 · Answer 2 · edited Dec 15 '20 at 15:18

1

You can use the sqllocaldb command line tool to create and delete instances, so delete the instance on 2016 (version 13.0) like this:

sqllocaldb delete "mssqllocaldb"

And then create that instance name on 2014 (version 12.0) using:

sqllocaldb create "mssqllocaldb" 12.0

There is also a nice .NET library available for doing this:

https://github.com/martincostello/sqllocaldb

edited Dec 15 '20 at 15:18

Wai Ha Lee

8,598
83
57
92

answered Oct 11 '16 at 11:11

ErikEJ

40,951
5
75
115

For whatever reason you can't revert mssqllocaldb to a previous version, you get an error "the parameter for the localdb instance api method is incorrect". The only workaround I have found is to edit registry key for mssqllocaldb under HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\UserInstances setting ParentInstance to MSSQL12E.LOCALDB. Then before starting the instance, go to C:\Users\you\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\mssqllocaldb and remove all files and copy from the folder above, "ProjectsV12". Then start the instance. Thanks Microsoft! – LMK Jun 22 '17 at 21:38

How can I specify to use SQL Server LocalDb 2014 rather than SQL Server LocalDb 2016 in the connection string?

2 Answers2

Linked