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

Question

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?

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

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.

Answer 1

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:

Prerequisites:

1. Microsoft SQL LocalDb engine
2. Microsoft SQL Server Management Studio

Steps:

1. Open command prompt

2. Run SqlLocalDb info to list currently installed LocalDb instances. There should be at least v11.0 for Sql Server 2012/Visual Studio 2012 or MSSQLLocalDB for 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.0 or (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

In Object Explorer you should now see the newly created DB, while in Windows Explorer you should now see the newly created .mdf and .ldf files at specified path.

Answer 2

Edit 2022: nowadays I'd probably rather user a SQL Server instance on Docker rather that using localdb. :)

--

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.

LocalDB and 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?

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

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

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 Server\120\Tools\Binn
SET sqlCmdDir=C:\Program Files\Microsoft SQL Server\120\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!

Answer 3

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

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

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

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.

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.

Answer 4

Just use CREATE DATABASE statement

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