Developing with LocalDb

Developing with LocalDb

posted in dev-setup on  • 

Tutorial on how to use LocalDb as a lightweight Sql Server substitution for development purposes.

Installation

It’s a feature of SQL Server Express. Make sure to select it during installation using the Express Advanced or LocalDb packages.
It can also be installed with the Visual Studio Installer. There it is a part of the .NET desktop development workload.

Usage

LocalDb can usually be found in

C:\Program Files\Microsoft SQL Server\130\Tools\Binn\SqlLocalDB.exe

The 130 is the version number of SQL Server 2016.

# List all instances
SqlLocalDB.exe info | % { SqlLocalDB.exe info $_ }

The default instance is called (LocalDb)\MSSQLLocalDB. It can be used as connectionstring for SSMS or Azure Data Studio using Windows Authentication.

Run SqlLocalDB.exe without arguments to get help on all CLI parameters.

# Create a new instance. -s for auto-start
# When "instance name" is omitted, MSSQLLocalDB is used
create ["instance name" [version-number] [-s]]
delete ["instance name"]

# -i request with NOWAIT option. -k kill without contact
stop ["instance name"] [-i|-k]
start ["instance name"]

share ["owner SID or account"] "instance name" "shared name"
unshare|u ["shared name"]

trace|t on|off
versions|v: Lists all LocalDb version-numbers installed

The data is stored in

%LOCALAPPDATA%\Microsoft\Microsoft SQL Server Local DB\Instances

ConnectionString

<add name="MyContext" 
   connectionString="Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=MyDatabaseName;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\MyDb.mdf" 
   providerName="System.Data.SqlClient" 
/>

Other interesting reads
Tags: sql tutorial