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