Seeding SQL Server LocalDB v11: A Developer's Guide

A practical guide to working with SQL Server LocalDB v11, creating databases with Entity Framework, and running seed scripts using PowerShell

Introduction

SQL Server LocalDB is a lightweight version of SQL Server Express designed for developers. While newer versions are available, many legacy projects still use LocalDB v11. This guide walks through the complete process of dropping, recreating, and seeding a LocalDB v11 database using Entity Framework Core and PowerShell.

Prerequisites

SQL Server LocalDB v11 installed
.NET Core/5+ project with Entity Framework Core
PowerShell with SQL Server module
Basic understanding of Entity Framework migrations

The Challenge: LocalDB v11 Quirks

LocalDB v11 has some unique characteristics that can trip up developers:

Instance naming: Uses (localdb)\v11.0 or (localdb)\mssqllocaldb depending on configuration
Connection issues: Often requires explicit instance startup
Tool compatibility: Some newer SQL tools may not work seamlessly with v11

Step 1: Starting the LocalDB Instance

Before working with LocalDB, ensure the instance is running:

# Start the LocalDB instance
sqllocaldb start mssqllocaldb

# Verify it's running
sqllocaldb info mssqllocaldb

If you’re using the v11.0 instance specifically:

sqllocaldb start v11.0

Step 2: Database Recreation with Entity Framework

Instead of manually dropping the database (which can be problematic with older sqlcmd versions), use Entity Framework:

# Navigate to your project directory
cd YourProjectName

# Apply migrations to recreate the database
dotnet ef database update

This approach:

Handles database creation automatically
Applies all pending migrations
Works consistently across different LocalDB versions
Avoids connection string issues

Step 3: Running Seed Scripts with Invoke-Sqlcmd

The key to successful seeding is using PowerShell’s Invoke-Sqlcmd cmdlet, which is more reliable than the standalone sqlcmd utility with LocalDB v11.

Basic Syntax

Invoke-Sqlcmd -ServerInstance "(localdb)\mssqllocaldb" -Database "YourDatabase" -InputFile "path\to\script.sql"

Real-World Example

# Run a seed script
Invoke-Sqlcmd -ServerInstance "(localdb)\mssqllocaldb" -Database "CDISV2Core" -InputFile "SQL/InitialSeed.sql"

# Run inline queries for verification
Invoke-Sqlcmd -ServerInstance "(localdb)\mssqllocaldb" -Database "CDISV2Core" -Query "SELECT COUNT(*) as RecordCount FROM YourTable"

Common Issues and Solutions

Issue 1: “Subquery returned more than 1 value”

Problem: Using subqueries without TOP clause in foreign key lookups.

Bad:

DECLARE @UserId nvarchar(450) = (SELECT Id FROM AspNetUsers WHERE Email = 'user@example.com')

Good:

DECLARE @UserId nvarchar(450) = (SELECT TOP 1 Id FROM AspNetUsers WHERE Email = 'user@example.com')

Issue 2: Foreign Key Constraint Violations

Problem: Referencing non-existent IDs or using literal values instead of foreign keys.

Bad:

INSERT INTO PhoneCommunication (CallLength, ...) VALUES (15, ...)  -- Using literal number

Good:

DECLARE @CallLength15Id int = (SELECT TOP 1 ID FROM CallLength WHERE CallLength = '15-30 minutes')
INSERT INTO PhoneCommunication (CallLength, ...) VALUES (@CallLength15Id, ...)

Issue 3: Connection Timeouts

Problem: LocalDB instance not started or incorrect connection string.

Solution:

# Ensure LocalDB is running
sqllocaldb start mssqllocaldb

# Use the exact connection string from your appsettings.json
# Example: "Server=(localdb)\mssqllocaldb;Database=YourDB;Trusted_Connection=True;MultipleActiveResultSets=true"

Best Practices for Seed Scripts

1. Use Idempotent Scripts

Always check for existing data before inserting:

IF NOT EXISTS (SELECT 1 FROM Users WHERE Email = 'test@example.com')
BEGIN
    INSERT INTO Users (Email, Name) VALUES ('test@example.com', 'Test User')
END

2. Handle Dependencies Properly

Ensure reference data is created before dependent data:

-- Create lookup tables first
INSERT INTO CallLength (CallLength, ChangeBy, ChangeDate, ChangeType)
VALUES ('5-10 minutes', 'System', GETDATE(), 1)

-- Then use the IDs in dependent tables
DECLARE @CallLengthId int = (SELECT TOP 1 ID FROM CallLength WHERE CallLength = '5-10 minutes')
INSERT INTO PhoneCommunication (CallLength, ...) VALUES (@CallLengthId, ...)

3. Use Transactions for Complex Seeds

BEGIN TRANSACTION
BEGIN TRY
    -- Your seed operations here
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
    THROW
END CATCH

Verification and Testing

After seeding, verify your data:

# Check row counts
Invoke-Sqlcmd -ServerInstance "(localdb)\mssqllocaldb" -Database "CDISV2Core" -Query "
SELECT 'Users' as TableName, COUNT(*) as Count FROM AspNetUsers
UNION ALL
SELECT 'Episodes', COUNT(*) FROM Episode  
UNION ALL
SELECT 'CallLength', COUNT(*) FROM CallLength"

Troubleshooting Connection Issues

If you encounter connection problems:

Check LocalDB instances:

sqllocaldb info

Verify connection string: Ensure it matches your launchSettings.json:

{
  "environmentVariables": {
    "DB_CONNECTION": "Server=(localdb)\\mssqllocaldb;Database=CDISV2Core;Trusted_Connection=True;MultipleActiveResultSets=true"
  }
}

Test basic connectivity:

Invoke-Sqlcmd -ServerInstance "(localdb)\mssqllocaldb" -Query "SELECT @@VERSION"

Complete Workflow Example

Here’s a complete PowerShell workflow for recreating and seeding a LocalDB v11 database:

# 1. Ensure LocalDB is running
sqllocaldb start mssqllocaldb

# 2. Navigate to project directory
cd CDISV2Core

# 3. Recreate database with EF migrations
dotnet ef database update

# 4. Run seed script
Invoke-Sqlcmd -ServerInstance "(localdb)\mssqllocaldb" -Database "CDISV2Core" -InputFile "SQL/InitialSeed.sql"

# 5. Verify seeding
Invoke-Sqlcmd -ServerInstance "(localdb)\mssqllocaldb" -Database "CDISV2Core" -Query "SELECT COUNT(*) FROM Episode"

Conclusion

Working with LocalDB v11 requires understanding its quirks and using the right tools. The combination of Entity Framework for schema management and PowerShell’s Invoke-Sqlcmd for data seeding provides a robust, repeatable process for database setup.

Key takeaways:

Always use Invoke-Sqlcmd over standalone sqlcmd for LocalDB v11
Start with Entity Framework migrations for schema creation
Write idempotent seed scripts with proper error handling
Use TOP 1 in subqueries to avoid “multiple values” errors
Verify your data after seeding

This approach ensures consistent database setup across development environments and reduces the friction often associated with LocalDB v11 development.

This guide was created based on real-world experience migrating a legacy ASP.NET application to .NET Core with LocalDB v11 constraints.

Seeding SQL Server LocalDB v11: A Developer’s Guide