.net 如何手动创建一个 mdf 文件供 localdb 使用？

How to manually create a mdf file for localdb to use?

提问by George Mauer

I'm setting up some unit tests for testing work done with a database. I would like to use localdb v11 but first I need to create the database. How exactly do I do this?

我正在设置一些单元测试来测试使用数据库完成的工作。我想使用 localdb v11，但首先我需要创建数据库。我该怎么做？

simply connecting to (localdb)v11in sql management studio connects me to the database that (I assume) is in C:\Users\George\. How do I specify a new one?

只需连接到(localdb)v11sql management studio 即可将我连接到（我假设）位于C:\Users\George\. 如何指定一个新的？

The code uses manual ADO.Net, not Entity Framework so as far as I know I cannot rely on it to simply create the database.

该代码使用手动 ADO.Net，而不是实体框架，据我所知，我不能依赖它来简单地创建数据库。

回答by Stan

Just use CREATE DATABASE statement

只需使用 CREATE DATABASE 语句

SqlConnection connection = new SqlConnection(@"server=(localdb)\v11.0");
using (connection)
{
    connection.Open();

    string sql = string.Format(@"
        CREATE DATABASE
            [Test]
        ON PRIMARY (
           NAME=Test_data,
           FILENAME = '{0}\Test_data.mdf'
        )
        LOG ON (
            NAME=Test_log,
            FILENAME = '{0}\Test_log.ldf'
        )",
        @"C:\Users\George"
    );

    SqlCommand command = new SqlCommand(sql, connection);
    command.ExecuteNonQuery();
}

回答by superjos

I know, old question, but I find the following way still relevant and quick. Here are the full steps, but actually only the last part is really relevant:

我知道，老问题，但我发现以下方法仍然相关且快速。这是完整的步骤，但实际上只有最后一部分才是真正相关的：

Pre-requisites:

先决条件：

1. MS Sql LocalDb engine
2. MS Sql Server Management Studio

1. MS Sql LocalDb 引擎
2. MS SQL Server 管理工作室

Steps:

脚步：

1. Open command prompt
2. Run SqlLocalDb infoto list currently installed LocalDb instances. There should be at least v11.0for Sql Server 2012/Visual Studio 2012 or MSSQLLocalDBfor Sql Server 2014/Visual Studio 2015
3. Open Sql Server Management Studio, or show Connect dialog if already running
4. Use Server name (localdb)\v11.0or (localdb)\MSSQLLocalDB, whichever you're interested into. Select Windows Authentication
5. Create a new query

6. Paste the following template, adapting your path and names as needed:

   CREATE DATABASE __YourDbName__ ON (
     NAME='__YourDbName__', 
     FILENAME='YourDrive:\Your\path\to\data\files\__YourDbName__.mdf')
   

7. Run query

8. Refresh Object Explorer list of Databases

1. 打开命令提示符
2. 运行SqlLocalDb info以列出当前安装的 LocalDb 实例。至少应该有v11.0Sql Server 2012/Visual Studio 2012 或MSSQLLocalDBSql Server 2014/Visual Studio 2015
3. 打开 Sql Server Management Studio，或者如果已经运行则显示连接对话框
4. 使用您感兴趣的服务器名称(localdb)\v11.0或(localdb)\MSSQLLocalDB。选择 Windows 身份验证
5. 创建新查询

6. 粘贴以下模板，根据需要调整您的路径和名称：

   CREATE DATABASE __YourDbName__ ON (
     NAME='__YourDbName__', 
     FILENAME='YourDrive:\Your\path\to\data\files\__YourDbName__.mdf')
   

7. 运行查询

8. 刷新数据库的对象资源管理器列表

In Object Explorer you should now see the newly created DB, while in Windows Explorer you should now see the newly created .mdfand .ldffiles at specified path.

在对象资源管理器，你现在应该看到新创建的数据库，而在Windows资源管理器，你现在应该看到新创建.mdf和.ldf文件在指定的路径。

HTH

HTH

回答by Alen Siljak

Not sure what you mean by "manually". I'll add an option using Visual Studio 2013 and LocalDb:

不确定您所说的“手动”是什么意思。我将使用 Visual Studio 2013 和 LocalDb 添加一个选项：

Open Server Explorer, right-click on Data Connections, select Create New SQL Server Database. For "Server Name" use "(LocalDB)\v11.0".

打开服务器资源管理器，右键单击数据连接，选择创建新的 SQL Server 数据库。对于“服务器名称”，请使用“(LocalDB)\v11.0”。

There is another option, as described herebut it requires installation of SQL Server Data Tools. A version of the instructions for Visual Studio 2012 is also available.

还有另一种选择，如此处所述，但它需要安装 SQL Server Data Tools。Visual Studio 2012 的说明版本也可用。

Since you also mention SQL Server Management Studio, you can simply connect to the LocalDb instance and right-click on Databases, then Create, the standard way. It is more-or-less a regular SQL Server instance and all standard operations will function as usual.

由于您还提到了 SQL Server Management Studio，您可以简单地连接到 LocalDb 实例并右键单击“数据库”，然后按标准方式单击“创建”。它或多或少是一个常规的 SQL Server 实例，所有标准操作都将照常运行。

Creating the database can also, obviously, be done from the application code as well, but that requires setting up appropriate database permissions. Depending on your environment that may or may not be a good idea.

显然，也可以通过应用程序代码创建数据库，但这需要设置适当的数据库权限。根据您的环境，这可能是也可能不是一个好主意。

回答by codea

If you are looking (like me) for a way to do that outside your code, you may want to consider using a .bat script to do that. I keep it in the solution as a .txt file that I can use when I need to setup the development environment again.

如果您正在寻找（像我一样）在代码之外执行此操作的方法，您可能需要考虑使用 .bat 脚本来执行此操作。我将它作为 .txt 文件保存在解决方案中，当我需要再次设置开发环境时可以使用它。

LocalDB and SQLCmd

LocalDB 和 SQLCmd

This script is assuming that LocalDB is installed. I could not find clear info about it yet but it seems it can be installed with visual studio 2012 and 2015 if you are using entity framework. If this is not the case, you can install it from a standalone installer or from the SQL Server express download page ( you can chose it as the download you want. More details here: How to install localdb separately?

此脚本假设已安装 LocalDB。我还没有找到关于它的明确信息，但如果您使用实体框架，它似乎可以与 Visual Studio 2012 和 2015 一起安装。如果不是这种情况，您可以从独立安装程序或从 SQL Server express 下载页面安装它（您可以选择它作为您想要的下载。更多详细信息在这里：如何单独安装 localdb？

SQLCmd can be dowloaded the same way from the SQLServer Feature Pack, look for SQLCmdlnUtility.msi. Here is the linkfor the 2012 version.

可以从 SQLServer Feature Pack 中以相同的方式下载 SQLCmd，查找 SQLCmdlnUtility.msi。这是2012 版的链接。

You may need to change the LocalDbDir and SQLCmdDir path if you don't have the same version as me.

如果您的版本与我不同，则可能需要更改 LocalDbDir 和 SQLCmdDir 路径。

script

脚本

:: Script to Create Local DB Instance and a database

::echo setting variables - Default Server is v11 but it may be useful to evolve in a server instance of your own...
SET localdDbDir=C:\Program Files\Microsoft SQL Server0\Tools\Binn
SET sqlCmdDir=C:\Program Files\Microsoft SQL Server0\Tools\Binn
SET SRV_NAME=your-dev-srv
SET DB_NAME=your-dev-db
SET DB_PATH=C:\CurDev\Temp

echo Creates the localDB server instance
pushd "%localdDbDir%"
:: uncomment those lines if you want to delete existing content
::SqlLocalDb stop %SRV_NAME%
::SqlLocalDb delete %SRV_NAME%
SqlLocalDb create %SRV_NAME%
SqlLocalDb start %SRV_NAME%
popd

echo Create the database intance
pushd "%sqlCmdDir%"
sqlcmd -S "(localdb)\%SRV_NAME%" -Q "CREATE DATABASE [%DB_NAME%] ON PRIMARY ( NAME=[%DB_NAME%_data], FILENAME = '%DB_PATH%\%DB_NAME%_data.mdf') LOG ON (NAME=[%DB_NAME%_log], FILENAME = '%DB_PATH%\%DB_NAME%_log.ldf');"
popd
echo completed

Hope this helps!

希望这可以帮助！