Reputation: 122232
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.
Upvotes: 44
Views: 57471
Reputation: 12745
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:
Steps:
Open command prompt
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
Open Sql Server Management Studio, or show Connect dialog if already running
Use Server name (localdb)\v11.0
or (localdb)\MSSQLLocalDB
, whichever you're interested into. Select Windows Authentication
Create a new query
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')
Run query
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.
Upvotes: 37
Reputation: 1571
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.
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 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!
Upvotes: 8
Reputation: 2595
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.
Upvotes: 8
Reputation: 1999
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();
}
Upvotes: 55