LocalDB: Where is My Database?


As I mentioned in the previous post LocalDB handles database files differently than regular SQL Server.

The regular SQL Server assumes that it is actively managed by a DBA. The DBA carefully configures SQL Server during the installation and afterwards. The DBA decides where SQL Server binaries are installed. The DBA also decides where the system database files are located. The location of the system databases also becomes the default location for all user databases.

LocalDB is different. It’s built to be used by developers, not DBAs. And we have heard developers saying loud and clear that they just want to focus on their databases and database code. Therefore our primary design goal was to eliminate all the server configuration and management that is getting in their way. As a result LocalDB stores all the system databases deep inside the “hidden” AppData folder in the user profile. For example, after playing with the Automatic Instance in the previous post, I find this folder in my user profile:

This folder constitutes my Automatic LocalDB Instance. Any good DBA will explain in great and painful details what these files are, but if you are a developer this is the first and the last time you will look at them. Which is great, but left us with an interesting question. If the location of system databases is hidden from the developer, what should the default location for the databases created by the developer be? Creating user databases in a hidden folder didn’t seem like the right design. We considered My Documents too, but that could give the impression that SQL Server databases are portable documents. Plus half of our team was using some sort of document synchronization solution, like Live Mesh. Those would quickly destroy the database files and we thought they will only get more popular over time, thus more likely to wreck havoc in the future.

In the end we decided to create the database files in the root of the user profile. On most machines it is located in C:\Users\user-name folder

In the spirit of a scientific approach, let’s try it out. Open SSMS and connect to your Automatic Instance:

Then create database foo without specifying the location for its files:

create database foo

Open Windows Explorer and navigate to your profile (typing %USERPROFILE% in the address bar is a nice shortcut). If the database was created successfully there will be new files in this folder, foo.mdf and foo_log.ldf that represent your database. QED.

Given that user profile folder is likely not the best location to store database files, we advise developers creating databases to always specify the location for the database files, like in this T-SQL example:

create database foo on (name=‘foo’, filename=‘c:\DBs\foo.mdf’)

– Krzysztof Kozielczyk

Send us Your Feedback

Please share your feedback with us! Just start a thread on SQL Express Forum, hit the “Email Author” button on this post or file a Connect item!


Comments (13)

  1. Papy Normand says:

    Excellent article.

    A little remark : I have to go to the end of this article to check who has written it and i thought : the new Mike Wachal is arrived ( it will be difficult to forgive Mike as i consider him as my mentor on SQL Server Express, but you are now as good as him )

    It was a pleasure to read it …. ( do you know when a six starts will be possible ? )

  2. Krzysztof Kozielczyk - MSFT says:

    Thank you Papy, I'm glad to see you still on top of SQL Server Express issues 🙂

    It's an honor to have my humble efforts compared to Mike's contributions!

  3. Kelly Jones says:

    Perfect post. Here’s a great tool that lets you build any type of database apps for web and mobile fast and without coding http://www.caspio.com/

  4. Raffaele Rialdi says:

    Please change the location of the localdb databases.

    We, as developers, run more and more on SSD hard drives and those drives are still very small in size.

    This is a big issue at the moment.

    Furthermore it would be nicer to have the db in the project folder so that I can safely move the entire project over another pc. And I would prefer not to use AttachDBFilename…

    Thanks

  5. Thanks for your comments Raffaele. When you say, "Please change the location of the localdb databases", I presume you mean the system databases, log files, etc? Unless you use a lot of tempdb space, these use around 30MB per instance in the user profile. This does not seem excessive to me, since you can locate user databases anywhere you want. With a little more trouble, you can actually move tempdb elsewhere if you need to.

    User DBs will be in the project folder if you use, for example, SQL Server Data Tools.

    You do not need to use "AttachDBFileName" to attach a database to an instance. You can also use (as with any SQL Server instance) "CREATE DATABASE … FOR ATTACH", and then just refer to it via its DB name.

  6. Shashanka says:

    Is it possible to change User DBs path programmatically? I mean not using SQL scripts. Ability to use app.config file for this would also be good.

    Thanks

  7. Don says:

    We considered My Documents too, but that could give the impression that SQL Server databases are portable documents

    When you get a new pc/notebook, will the localdb's be usable on the new pc?  What is the process to move them to the new device.

  8. Ахмед says:

    а почему не удается в проекте, создав базу данных, создать таблицу? Я ее создаю, но она не отображается в таблицах

  9. Ahmed wrote: "and why can't the project by creating a database, create a table? I'm creating, but it does not appear in the tables"

    Ahmed, can you describe in more detail exactly what you are doing?

  10. Ali says:

    Regarding the deploying an application which connected with database. My question do I need to install  SQL Server Express on other PC? OR I have to install only LocalDB plus the xxxxx.MDF.

  11. lol says:

    i have a good time at home with my i pad 🙂

  12. warmac says:

    Deployment, Deployment, Deployment without aggravation has been lost since VB6 was retired. The developer that came from Borland/creator of Delphi, and then migrated (no pun intended) to Microsoft and screwed up their high level languages should have been fired by now. But the pain and misery continues.  

    ComponentOne makes the dataextender that is a great product if you just want to use a simple database with a few tables and you don't want to get into the weeds with the table adapter, a dataset, a dataview, a table, the binding source and then binding this all to a simple grid and then hoping and praying that you drug everything to the form and it works. Miracles happen everyday and the living proof of this is using Visual Studio to do database programming.

    I feel like the dog with a clothes pin on its tail.

  13. vikas says:

    C:Program Files (x86)Microsoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATA