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
Anonymous
November 17, 2011
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 ? )Anonymous
December 05, 2011
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!Anonymous
February 24, 2012
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/Anonymous
April 22, 2012
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... ThanksAnonymous
April 23, 2012
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.Anonymous
July 08, 2012
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. ThanksAnonymous
July 27, 2012
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.Anonymous
September 27, 2012
а почему не удается в проекте, создав базу данных, создать таблицу? Я ее создаю, но она не отображается в таблицахAnonymous
September 27, 2012
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?Anonymous
December 11, 2012
The comment has been removedAnonymous
April 24, 2013
i have a good time at home with my i pad :)Anonymous
December 01, 2014
The comment has been removedAnonymous
December 05, 2014
C:Program Files (x86)Microsoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATA