Daily Archives: 14 February 2014

LocalDb – Where the Dbs Live and Some Simple Facts

I always forget where to find the mdf files which are created by localdb (hence this post).
If we take a look at the connection string, it’s not too instructional as to the location:

connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=ContosoUniversity2;Integrated Security=SSPI;"

In this post, I’ll identify the locations where you are likely to find that elusive localdb database.

Local AppData
Where you haven’t created the database yourself (perhaps you downloaded a project which creates and seeds the database via EF Code First), it can usually be found in this directory:
%AppData%\..\Local\Microsoft\Microsoft SQL Server Local DB\Instances\v11.0. Note the two dots before the string, Local, in that path. Without that, you’ll be dumped into the Roaming directory, which is not the right place.

User Profile
If you don’t find it there, another place it is often found is in the User Profile – %USERPROFILE%. When you create a new database in Sql Server, either through the GUI or by running the following Sql, the default path is in the %USERPROFILE% location:

create database SpiderMonkey

SQL Server Management Studio
Ultimately, you can dictate where the localdb mdf/ldf files for a database are created. Either through the GUI in SQL Server Management Studio, or by running the following into SQL Server Management Studio’s query window:

create database SpiderMonkey on (name='SpiderMonkey', filename='c:\DBs\SpiderMonkey.mdf')

Visual Studio
If you create a localdb database using Visual Studio, the default location is: %USERPROFILE%\AppData\Local\Microsoft\VisualStudio\SSDT
The following 2 pictures outline the workflow of using Visual Studio to create a localdb database. As you can see from the 2nd picture, you can choose where the mdf files are created:


As a final comment, the whole idea of localdb is that we developers should not need to be concerning ourselves with issues about the database. The team who created localdb did so with the intention of abstracting away the database so that we could focus on development. But it is still nice to know where to find these things, even if just to clean up your hard drive!