LocalDB: change SQL Server default location

Question

I wonder if it is possible to change default location of (LocalDB). When you create it with SqlLocalDB.exe default location is

C:\Users\userId\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MyDB

And I believe this path is used in (LocalDB) in connection strings (auto generated by creator of *.dbml files):

<connectionStrings>
    <add name="MyApp.Properties.Settings.MyConnectionString"
         connectionString="Data Source=**(LocalDB)**\MyDB;Initial Catalog=sthDB;Integrated Security=True"
         providerName="System.Data.SqlClient" />
</connectionStrings>

Answer 1

I've also been trying to customise the instances location, and have found a solution. As alluded to in previous posts, it appears that it defaults to %LOCALAPPDATA%\Microsoft\Microsoft SQL Server Local DB\Instances. After some experimentation, it seems that the SQLLocabDB command line utility uses the %USERPROFILE% environment variable (rather than %LOCALAPPDATA%) to find this location.

The following worked for me (using SQLLocalDB from a command prompt):

C:\Users\dan.smith>echo %USERPROFILE%
C:\Users\dan.smith

C:\Users\dan.smith>set USERPROFILE=c:\temp

C:\Users\dan.smith>echo %USERPROFILE%
c:\temp

C:\Users\dan.smith>mkdir c:\temp\AppData\Local

C:\Users\dan.smith>sqllocaldb create test
LocalDB instance "test" created with version 13.0.1100.286.

C:\Users\dan.smith>cd C:\temp\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\test

C:\temp\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\test>dir /w
 Volume in drive C has no label.
 Volume Serial Number is 4A71-7A6F

 Directory of C:\temp\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\test

[.]                                      [..]
error.log                                error1.log
log.trc                                  master.mdf
mastlog.ldf                              model.mdf
modellog.ldf                             msdbdata.mdf
msdblog.ldf                              system_health_0_131061520581180000.xel
tempdb.mdf                               templog.ldf
              12 File(s)     46,701,550 bytes
               2 Dir(s)  117,107,499,008 bytes free

As shown, this created my LocalDB instance under c:\temp, albeit inheriting the original folder hierarchy from "AppData" onward (which seems unchangeable). Note that it was also necessary to create the "AppData\Local" part of the folder hierarchy manually, otherwise it fails.

The next issue is actually connecting to this database from a C# application. To do this, the %USERPROFILE% environment variable must be set to the same location as above, i.e.:

Environment.SetEnvironmentVariable("USERPROFILE", "c:\\temp");

This should be done prior to establishing a DB connection. Probably best to do this somewhere in the entry point of the application.

All in all this is a bit of a hack, but it at least gives one the option to store things somewhere else other than in "c:\users...".

UPDATE

It's worth noting that the idea here was to only change the %USERPROFILE% environment variable for the currently running process, rather than machine-wide. This is the behaviour when using set in the command prompt. The Environment.SetEnvironmentVariable method overload mentioned above also defaults to this behaviour, though it's probably better to be more explicit with something like:

Environment.SetEnvironmentVariable("USERPROFILE", "c:\\temp", EnvironmentVariableTarget.Process);