LocalDB：更改SQL Server的默认位置

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

我想知道是否可以更改（LocalDB）的默认位置。当您使用SqlLocalDB.exe创建它时，默认位置是

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

我相信这个路径在（LocalDB）连接字符串中使用（由* .dbml文件的创建者自动生成）：

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

2 个解决方案

#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.

我也一直在尝试自定义实例位置，并找到了解决方案。正如之前的帖子中所提到的，它似乎默认为％LOCALAPPDATA％\ Microsoft \ Microsoft SQL Server本地数据库\实例。经过一些实验，似乎SQLLocabDB命令行实用程序使用％USERPROFILE％环境变量（而不是％LOCALAPPDATA％）来查找此位置。

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

以下内容适用于我（从命令提示符使用SQLLocalDB）：

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.

如图所示，这在c：\ temp下创建了我的LocalDB实例，尽管从“AppData”开始继承原始文件夹层次结构（这似乎是不可更改的）。请注意，还必须手动创建文件夹层次结构的“AppData \ Local”部分，否则会失败。

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.:

下一个问题实际上是从C＃应用程序连接到此数据库。为此，必须将％USERPROFILE％环境变量设置为与上面相同的位置，即：

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...".

总而言之，这有点像黑客，但它至少给了一个选项来存储除“c：\ users ...”之外的其他地方。

#2

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

#1