Introduction

SQL Server LocalDB is a lightweight developer-focused SQL Server runtime that stores database files locally, often in .mdf (data) and .ldf (log) format. You usually create a database with SQL commands and let SQL Server create files automatically, but there are cases where you want explicit file paths and names, such as reproducible local setups, migration testing, or tooling integration.

A common misconception is that you should handcraft an .mdf file directly in the filesystem. You should not do that. The correct approach is to instruct SQL Server to create or attach the file through T-SQL or sqlcmd. This article shows the safe workflow for creating, attaching, verifying, and troubleshooting LocalDB .mdf usage.

Core Sections

Confirm LocalDB instance availability

Before creating files, verify LocalDB installation and start an instance.

1sqllocaldb info
2sqllocaldb create "MSSQLLocalDB"
3sqllocaldb start "MSSQLLocalDB"
4sqllocaldb info "MSSQLLocalDB"

Most Windows setups already include MSSQLLocalDB, but explicit checks prevent connection confusion.

Create .mdf and .ldf using SQL

Use CREATE DATABASE with file paths. SQL Server initializes file structure correctly.

1CREATE DATABASE DevInventory
2ON PRIMARY
3(
4    NAME = N'DevInventory_Data',
5    FILENAME = N'C:\Data\DevInventory.mdf',
6    SIZE = 20MB,
7    FILEGROWTH = 10MB
8)
9LOG ON
10(
11    NAME = N'DevInventory_Log',
12    FILENAME = N'C:\Data\DevInventory_log.ldf',
13    SIZE = 10MB,
14    FILEGROWTH = 10MB
15);

Run via SSMS, Azure Data Studio, or sqlcmd connected to (localdb)\MSSQLLocalDB.

Attach an existing .mdf

If an .mdf already exists from another project, attach it instead of recreating.

CREATE DATABASE LegacyDb
ON (FILENAME = N'C:\Data\LegacyDb.mdf')
FOR ATTACH;

If the log file is missing, use FOR ATTACH_REBUILD_LOG cautiously and only on trusted development data.

CREATE DATABASE LegacyDb
ON (FILENAME = N'C:\Data\LegacyDb.mdf')
FOR ATTACH_REBUILD_LOG;

Connect from .NET with AttachDbFilename

LocalDB connection strings can attach files directly when needed.

1var cs = @"Server=(localdb)\MSSQLLocalDB;
2Integrated Security=true;
3AttachDbFilename=C:\Data\DevInventory.mdf;
4Database=DevInventory;";

Prefer explicit database creation first, then connect by database name for cleaner lifecycle management.

Verify file mapping and state

Confirm SQL Server sees the files and database is online.

1SELECT
2    DB_NAME(database_id) AS db_name,
3    name,
4    physical_name,
5    state_desc
6FROM sys.master_files
7WHERE DB_NAME(database_id) = 'DevInventory';

This query is useful after moving files or changing machine paths.

Detach and move database files safely

If you need to relocate files, detach first and reattach.

USE master;
ALTER DATABASE DevInventory SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
EXEC sp_detach_db 'DevInventory';

Move files in filesystem, then attach with new path using CREATE DATABASE ... FOR ATTACH.

Common Pitfalls

• Trying to create a raw .mdf file manually without SQL Server metadata initialization.
• Using invalid paths or missing folder permissions, causing LocalDB file open failures.
• Attaching databases while files are locked by another SQL Server instance.
• Relying solely on AttachDbFilename without understanding database lifecycle and naming collisions.
• Rebuilding log files on important data without backups, risking corruption or data loss.

Summary

To manually create an .mdf for LocalDB, use SQL Server commands, not direct file creation. Start LocalDB, run CREATE DATABASE with explicit file paths, or attach existing .mdf files through FOR ATTACH. Validate file mapping in system views and manage moves with detach/attach workflows. With these practices, LocalDB databases remain reliable, reproducible, and easier to troubleshoot in development environments.

Teams benefit from scripting these steps in onboarding docs or setup scripts so every developer uses the same instance name and file layout. Standardized local database initialization reduces environment drift and avoids time-consuming path and permission issues during project setup. Include a quick health query in setup scripts to confirm attach success before application startup tries to run migrations. A predictable initialization routine also makes local debugging and CI troubleshooting significantly faster.