Getting Started with SQL Server 2017 Express LocalDB

By:   |   Comments (6)   |   Related: 1 | 2 | 3 | 4 | > Express Edition


Problem

In a previous tip, I walked through installation and some caveats with the first version of SqlLocalDb, which shipped with SQL Server 2012. Well, it’s been several major releases, and some of the aspects have changed, so I thought I would provide a refresh.

The purpose of SqlLocalDb has remained constant: To provide developers with an easy way to develop with SQL Server locally, on Windows, without the overhead, security, and maintenance of a full-time, proper instance. But for anyone who has used SQL Server and is new to SqlLocalDb, a few of the details are unintuitive.

Solution

The first step is making sure you’re on a supported operating system (Windows 8 / Server 2012 or above), and then download the SQL Server Express Edition installer here. I am using Windows 10 in these examples; if you are using Windows 8 or Windows Server 2012, make sure to consult KB #2681562 : Installing SQL Server on Windows Server 2012 or Windows 8. If you’re using Linux, sorry, but I think you’re out of luck.

Step 1: Install Microsoft SQL Server Express Localdb

To get just the SqlLocalDb MSI (45 MB) vs. the whole enchilada (700+ MB), choose the “Download Media” option to start downloading:

Express Edition installer

Pick your language, choose the LocalDB option, and pick a location to download the MSI:

Express Edition installer - download option

Then you’ll get a very big dialog to indicate success. Choose the Open Folder option:

Express Edition download - success

The folder will open, and you will see SqlLocalDB.msi. Launch this executable to start the wizard:

SqlLocalDB installer

You’ll have to accept a license agreement and then on the next screen click Install:

SqlLocalDB installer

At some point you will probably be prompted by UAC controls:

UAC prompt

Then it will finish:

SqlLocalDb installer - complete

Alternatively, if you already have a valid SQL Server 2017 install media, you can install SqlLocalDb from that installer, and avoid downloading the media above. Run Setup.exe and from the Installation Center choose “New SQL Server stand-alone installation or add features to an existing SQL Server 2017 instance.”

SQL Server 2017 installation center

Next, you’ll be offered to include any important updates (you should check this box unless you are on a very slow Internet connection – but don’t worry, we’re going to patch this installation with the latest Cumulative Update anyway):

SQL Server 2017 installer - Microsoft Update

Next, you’ll choose the type of installation to perform; you want “a new installation of SQL Server 2017,” even though that choice may not be the most intuitive:

SQL Server 2017 installer - Installation Type

You’ll have to accept the license terms in a new dialog, and then you’ll be asked for a product key. Here, just select the free Express Edition (other editions won’t offer a LocalDB installation):

getting started with sql server 2017 express localdb 011

Then on the Feature Selection screen, make sure you un-check the Database Engine Services option, which is selected by default (unless you also want to install a full-on SQL Server 2017 instance).

SQL Server 2017 installer - Feature Selection

Then scroll down and select the LocalDB option (I also selected Client Tools Connectivity).

SQL Server 2017 installer - Feature Selection

Click Next, Install, and then you should have this:

SQL Server 2017 installer - Success

Step 2: Patch Microsoft SQL Server 2017

Before you get started using SqlLocalDb, you should patch SQL Server 2017 to the latest Cumulative Update. The reason is that there was initially a critical bug that prevented the creation of database files due to a missing slash in the file path.

This problem was fixed in CU #6 (see KB #4096875), but at the time of writing, the latest Cumulative Update available was CU #9. You should typically use the latest CU available; you can always get the latest CU here.

At the beginning of the install it will not indicate anything about SqlLocalDb, but just check all the boxes you can and proceed. At the end you will see confirmation that SqlLocalDb has been patched (in this case I also updated a SQL Server 2017 instance from CU #8 to CU #9):

SQL Server 2017 CU completion

Step 3: Install client tools and/or SQLCMD and/or PowerShell

If you don’t already have SSMS, or another way to connect to the SQL Server database, you’re not going to get very far. Rather than guide through the full installation I’ll just point you to the locations to get the most recent versions: 

Step 4: Create an localdb instance via SQLLocalDB Command Line

At the command line, you can interact using the SqlLocalDb utility to configure the instance of localdb. Start with getting information about the installation:

C:\> SqlLocalDb info			

Result:

MSSQLLocalDB			

This used to return the localdb version number (in the original article, and with the 2012 release, this returned v11.0.

Next, you can create an instance with the following command:

C:\> SqlLocalDb create "MyInstance"			

Result:

LocalDB instance "MyInstance" created with version 14.0.3030.27.			

Check the info:

C:\> SqlLocalDb info "MyInstance"			

Result:

Name:                MyInstance
Version:             14.0.3030.27
Shared name:
Owner:               PEDRO\aaronbertrand
Auto-create:         No
State:               Running
Last start time:     7/20/18 10:44:51 AM
Instance pipe name:  np:\\.\pipe\LOCALDB#9EBB1CD2\tsql\query

The Instance pipe name may come in handy later, though I’ve found that a lot of the connectivity issues in earlier versions of this feature have gone away. Also, in older versions you had to explicitly start the instance, but it now starts automatically.

If you want to stop and drop the instance, use:

C:\> SqlLocalDb stop "MyInstance"
C:\> SqlLocalDb delete "MyInstance"

But don’t do that just yet. Evidence that this all works so far:

Command line interaction with LocalDB

Connection String for SQLCMD

Locate SQLCMD, making sure to use the newest version on your machine (your environment path may list an older version first). Look for the highest version in the Binn folders under C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\[version]\Tools\. You can connect to this instance using Windows authentication with the following code at the command line:

sqlcmd -S "(localdb)\MyInstance"			

Then we’re greeted with a line number prompt and can enter sql code on-demand. So something like:

SELECT @@VERSION;
GO
CREATE DATABASE blat;
GO
USE blat;
GO
CREATE TABLE dbo.splunge(Mort int);
GO
INSERT dbo.Splune(Mort) VALUES(1);
SELECT * FROM dbo.splunge;
GO

Yields:

SQLCMD interaction with LocalDB

Connect using Microsoft SQL Server Management Studio

Like with SQLCMD, you can connect using (localdb)\MyInstance from SSMS as well (in older versions you needed the pipe name I mentioned above):

SSMS Connection dialog

When you open Object Explorer, you’ll see the database and table we created, and you can interact with the instance just like any other SQL Server instance (with obvious exceptions, for example there is no SQL Server Agent node under Management):

SSMS Object Explorer

Connection String using PowerShell

Modern versions of PowerShell are also able to connect to LocalDB instances using the simple instance name format. With my default installation, though, I found I still needed to manually load SMO before it would connect and interact.

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
$s = "(localdb)\MyInstance";
$srv = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $sn;
$srv.ConnectionContext.LoginSecure = $true;
$srv.Databases | Select Name;

Proof it works:

PowerShell interaction with LocalDB

Conclusion

I hope this gives you a head start into playing with SqlLocalDb for local development. It can be a really useful way to build out a proof of concept or test a query or feature without installing a full-blown SQL Server instance.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Friday, May 3, 2024 - 6:54:56 AM - Peter Smith Back To Top (92214)
Still extremely useful and helpful in 2024. :)

Thursday, April 8, 2021 - 12:14:27 AM - Arun Back To Top (88494)
You rock bro......


Monday, December 9, 2019 - 1:31:39 PM - Charles Batwala Back To Top (83356)

Thank you very much for this article. It was was very useful.


Saturday, February 2, 2019 - 2:50:49 PM - exp_cj Back To Top (78943)

Thanks for this, it was very helpful. Getting this to work seems to be slightly different every time i do it.

A small point though is that connection to the LocalDb instance using SSMS (2017 client) is achieved using the instance name as "(LocalDb)\MyInstance" and it does seem to mind the case int he localdb.


Thursday, January 17, 2019 - 9:21:01 AM - Joseph Back To Top (78799)

 Very helpful walkthrough instruction. It solved my problem


Thursday, August 2, 2018 - 3:38:11 AM - Onuoha Miriam Ozioma Back To Top (76941)

 Thanks for all the tips 















get free sql tips
agree to terms