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>

Lockhart

Answer 1

You can't change the default, but you can change it for every database you create:

create database foo on (name='foo', filename='c:\DBs\foo.mdf')

http://blogs.msdn.com/b/sqlexpress/archive/2011/10/28/localdb-where-is-my-database.aspx

Berstine

I believe i should have ask is it possible to create instance of server in different location – Lockhart

Answer 2

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);

Wireless

It may be particular to the execution environment where I'm running it, but I also found it necessary to grant access through the NT account rather than a security group. Somebody else ran into this in a slightly different context here and the solution they were given is what finally got it working for me (in PowerShell: icacls $env:USERPROFILE /grant "$(whoami):(OI)(CI)(F)" – Ledbetter

It defaults to user profile folder instead in my case – Err

Answer 3

Welcome to 2021. So now it is pretty similar to what @dean described three years ago. You can't change the default in a regular way mentioned in the article View or Change the Default Locations for Data and Log Files, by using SSMS, Object Explorer, then selecting your SQLLocalDB instance and changing its properties on the Database Settings tab.

The best answer on "why?" which I found comes from the same source:

An instance of SQL Server Express LocalDB is an instance created by a user for their use. Any user on the computer can create a database using an instance of LocalDB, store files under their user profile, and run the process under their credentials. By default, access to the instance of LocalDB is limited to its owner.

BTW, maybe you know this, maybe not: you can fill the form and then look for the Script button at the top of the Server Properties dialog window. Then if you select Script Action to New Query Window from the dropdown menu list, you can see something like this (in a new query window):

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', REG_SZ, N'C:\Data'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'C:\Data'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'C:\Data'
GO

Unfortunately, it probably works only for "true" SQL Server, not SQL Local DB. When I tried to use it as an administrator, I saw this:

Msg 22002, Level 16, State 1, Line 2
RegCreateKeyEx() returned error 5, 'Access is denied.'
Msg 22002, Level 16, State 1, Line 4
RegCreateKeyEx() returned error 5, 'Access is denied.'
Msg 22002, Level 16, State 1, Line 6
RegCreateKeyEx() returned error 5, 'Access is denied.'

So there is one tip, especially if you are using .NET Core/5 and try to easily use Entity Framework migrations, and Code First approach to development. You can update appsettings.json file with the connection string defining your preferred default location in the field AttachDbFileName.

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=ContactManager;Trusted_Connection=True;MultipleActiveResultSets=true;AttachDbFileName=C:\\Data\\ContactManager.mdf;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*"
}

Midriff

I tried to use AttachDbFileName with "|DataDirectory|" and figuered it would point to an AppData-folder within my solution, but instead it also pointed to %USERPROFILE%\xxx.mdf (like it would without |DataDirectory|). – Empressement

@JensMander , this info comes from 2009 and SQL Server Compact edition, but I guess this is still valid, generally, because the concept behind LocalDb and Compact is similar. In summary: the DataDirectory property has to be set in the main application, so not by environment variables. The source link: social.msdn.microsoft.com/Forums/sqlserver/en-US/… – Midriff