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.

Solution 1

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

Solution 2

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

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.

HTH

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

Solution 4

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!

View more solutions

Comments

[Abhishek]

Ahh..I was just trying to figure out how to do it from sql manager studio but this works. The key was that I didn't realize there was a FILENAME parameter I could provide. How would I then use this database, with AttachDbFileName=.\\Test_data.mdf?

[Abhishek]

For completeness - I needed to calculate the path to the mdf at runtime since this is a unit test here is how I did that: datamart = String.Format(@"Data Source=(LocalDB)\v11.0;Integrated Security=True;AttachDbFileName='{0}\MockDatamart.mdf'", Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "assets"));

[Abhishek]

First of, the question is pretty clearly asking about in the code. More importantly, the SqlLocalDb and sqlcmd commands won't always be available in any environment. For this answer to be truly useful you should mention what global dependencies you're assuming. Finally, there should be absolutely no need to delete or recreate localdb instances, nor will there be any need to start one up as the sqlcmd command trying to access it will do that by itself.

[codea]

Thanks for the clarification George. I ended up doing this because I had discrepancies between two visual studio installations. This way I am certain I can start clean. And I struggled a lot to find how the VS can create this instance for me, so I ended up with this script. But I agree this might be an overkill solution. Furthermore, even though the question details does not ask for it, the title may suggest you will find such content into it google brought me on this page when looking for localdb manual creation. I hope this can help others though.

[Abhishek]

That's fair, but to be useful to others you should at least edit the question to explain how to get SqlLocalDb and sqlcmd available in your shell. Neither is available by default.

[ctrl-alt-delor]

There is something missing, probably a using, as the compiler dose not understand SqlConnection.

[Doug Kimzey]

This is a very clear explanation. While it may be there, I could not find a set of instructions like this in Microsoft Docs.