Managing SQL Server Express LocalDB Instances Through SqlLocalDB.exe Utility Tool

I love the feeling when I discover a tiny, hidden tool and SqlLocalDB.exe, a management utility tool for Microsoft SQL Server LocalDB which allows you to manage the LocalDB instances on your machine, is one of them. Let me show you what it is.
18 February 2016
3 minutes read
SQL Server
4

Related Posts

1 minutes read
Speaking at SQL in the City 2017, Register Now!
5 December 2017
Geek Talks
30
 Microsoft SQL Server
1
 SQL Server
4
1 minutes read
Slides of Introduction to Database Lifecycle Management Talk at /dev/summer 2015
28 June 2015
DLM
6
 Geek Talks
30
 SQL Release
2
 SQL Server
4
7 minutes read
How to Use Octopus Deploy Step Templates for SQL Release
6 April 2015
Continious Delivery
1
 DLM
6
 Octopus Deploy
1
 SQL Release
2
 SQL Server
4

I love the feeling when I discover a tiny, hidden tool which I can put into my daily software toolbox. I started to sense this feeling more and more lately with some amazing command line tools and I want to write about those here (famous last words) like I did for http-server and WAStorageEmulator.exe a while back. Today, I want to start this by writing about SqlLocalDB.exe, a management utility tool for Microsoft SQL Server Express LocalDB which allows you to manage the LocalDB instances on your machine such as creating, starting and stopping them. This is really handy if you are after creating lightweight SQL Server instances for temporary processing like we do as part of some DLM Automation Suite PowerShell cmdlets (e.g. Invoke-DlmDatabaseSchemaValidation).

image

I am not entirely sure hot you would install this tool and I failed to find out exactly how. However, it seems like this comes with the LocalDB installation and you can acquire that through here (also check out Scott Hanselman's ironic "Download SQL Server Express" blog post).

This may depend on the version you installed but I can locate the SqlLocalDB.exe under "C:\Program Files\Microsoft SQL Server\120\Tools\Binn" on my machine and after that, it's just executing the commands. For example, I can execute the info command to see the LocalDB instances I have in my machine:

C:\Program Files\Microsoft SQL Server\120\Tools\Binn>SqlLocalDB.exe info

MSSQLLocalDB
RedGateTemp

I can also list which versions of LocalDB I have installed on my machine:

C:\Program Files\Microsoft SQL Server\120\Tools\Binn>SqlLocalDB.exe versions

Microsoft SQL Server 2014 (12.0.2000.8)

Let's create our own instance through the create command:

C:\Program Files\Microsoft SQL Server\120\Tools\Binn>SqlLocalDB.exe create tugberk

LocalDB instance "tugberk" created with version 12.0.2000.8.

You can view the status of an instance through the info command by passing the instance name as an argument:

C:\Program Files\Microsoft SQL Server\120\Tools\Binn>SqlLocalDB.exe info tugberk

Name:               tugberk
Version:            12.0.2000.8
Shared name:
Owner:              TUGBERKPC\Tugberk
Auto-create:        No
State:              Stopped
Last start time:    2/18/2016 12:06:00 PM
Instance pipe name:

Let's start the instance through the start command:

C:\Program Files\Microsoft SQL Server\120\Tools\Binn>SqlLocalDB.exe start tugberk

LocalDB instance "tugberk" started.

We can see that it has started:

C:\Program Files\Microsoft SQL Server\120\Tools\Binn>SqlLocalDB.exe info tugberk

Name:               tugberk
Version:            12.0.2000.8
Shared name:
Owner:              TUGBERKPC\Tugberk
Auto-create:        No
State:              Running
Last start time:    2/18/2016 12:09:28 PM
Instance pipe name: np:\\.\pipe\LOCALDB#7F6D2993\tsql\query

Great, now I can connect to this instance using the provided magical, special instance pipe name:

image

Or, LocalDB server name by prefixing the instance name with "(localdb)\":

image

If you dig a little deeper, you will see that an instance of "sqlservr.exe" has been started for your LocalDB instance:

image

You can also see that all LocalDB data is stored under "%LOCALAPPDATA%\Microsoft\Microsoft SQL Server Local DB\Instances".

From there, you can treat this instance as a usual SQL Server instance and perform whatever operation you want to perform on it. Be aware that LocalDB has the same limitations as SQL Server Express.

image

Finally, you can stop the instance through the stop command:

C:\Program Files\Microsoft SQL Server\120\Tools\Binn>SqlLocalDB.exe stop tugberk

LocalDB instance "tugberk" stopped.

Further Information