SQL: How do I stop my database files going into the Users folder with localdb?

I’ve seen questions on the forums asking about where localdb stores its database files. Often, the question is about how to stop them going into the C:\Users folder. Clearly, that doesn’t seem to be a good spot for them.

localdb stores database files in the C:\Users area by default as it’s trying to find a location that the current user will have permission to write to. While I understand the rationale, that’s not where most users will want those files.

The answer is to not let localdb pick where the files will go.

Instead of writing:

CREATE DATABASE Sales;

use a more complete command like:

CREATE DATABASE Sales
ON
(
    NAME = 'Sales_Data',
    FILENAME = 'C:\SomeAppropriatePath\Sales.mdf'
)
LOG ON
(
    NAME = 'Sales_Log',
    FILENAME = 'C:\SomeAppropriatePath\Sales.ldf'
);

While you’re at it, check out the CREATE DATABASE syntax and examples for other options that might be useful to you, particularly the ones around filegrowth. (Hint: Use a fixed increment, not a percentage) Not everything will work on localdb but most will.

2020-05-08