的LocalDB：更改SQL Server默认位置 [英] LocalDB: change SQL Server default location

问题描述

我不知道是否有可能改变的默认位置（的LocalDB）。当您使用 SqlLocalDB.exe 创建默认位置是

  C： \Users\userId\AppData\Local\Microsoft\Microsoft SQL服务器本地DB\Instances\MyDB 
  

我相信这条道路在使用（的LocalDB）在连接字符串（由的创建者自动生成*。DBML 文件）：

 <&是connectionStrings GT; 
<添加名称=MyApp.Properties.Settings.MyConnectionString
的connectionString =数据源= **（的LocalDB）** \MyDB;初始目录= sthDB;集成安全性=真
的providerName =System.Data.SqlClient的/> 
< /&是connectionStrings GT; 
  

解决方案

我也一直在试图自定义实例位置，并已找到了解决办法。正如在以前的帖子躲避到，看来，它默认为％LOCALAPPDATA％\Microsoft\Microsoft SQL服务器本地DB\Instances。一些试验后，似乎SQLLocabDB命令行实用程序使用％USERPROFILE％的环境变量（而非％LOCALAPPDATA％）找到这个位置。

以下工作我（使用SQLLocalDB从命令提示符）：

  C：\Users\dan.smith>回声％USERPROFILE％
 C：\Users\dan.smith 
 
 C：\Users\dan.smith>设置USERPROFILE = C：\temp 
 
 C： \Users\dan.smith>回声％USERPROFILE％
C：\temp 
 
 C：\Users\dan.smith>的mkdir C：\temp\AppData\\ \\Local 
 
 C：\Users\dan.smith> sqllocaldb创建测试
的LocalDB实例的测试与13.0.1100.286版本创建的。 
 
 C：\Users\dan.smith> CD C：\temp\AppData\Local\Microsoft\Microsoft SQL服务器本地DB\Instances\test 
 
 C：\temp\AppData\Local\Microsoft\Microsoft SQL服务器本地DB\Instances\test> DIR / W驱动器C 
卷没有标签。 
卷序列号是C 4A71-7A6F 
 
目录：SQL \temp\AppData\Local\Microsoft\Microsoft服务器本地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文件（S）46701550字节
 2目录（S）117107499008字节免费
  

如图所示，这建立在c我的LocalDB实例：\temp，尽管是从应用程序数据继承原来的文件夹层次结构前进（这似乎不可更改）。请注意，它也有必要手动创建文件夹层次的AppData\Local的一部分，否则失败。

接下来的问题是实际连接到这个数据库从C＃应用程序。要做到这一点，％USERPROFILE％环境变量必须按上述设置为相同的位置，例如：

  Environment.SetEnvironmentVariable（ USERPROFILE，C：\\temp）; 
  

这之前，应先建立一个数据库连接完成的。可能是最好的做这某处应用程序的入口点。

这所有的一切是一个黑客位，但它至少给一个存储选项其他的事情其他地方比C：\users ......

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>

解决方案

I've also been trying to customise the instances location, and have found a solution. As eluded 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...".

这篇关于的LocalDB：更改SQL Server默认位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！