Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Getting Started with SQL Server 2017 Express LocalDB


By:   |   Last Updated: 2018-08-01   |   Comments (2)   |   Related Tips: More > 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.

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

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):

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 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 SQL Server, 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 instance

At the command line, you can interact using the program name SqlLocalDb. Start with getting information about the installation:

C:\> SqlLocalDb info			

Result:

MSSQLLocalDB			

This used to return the 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

Connect using 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 the following from the command line:

sqlcmd -S "(localdb)\MyInstance"			

Then we’re greeted with a line number prompt and can enter commands at will. 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 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

Connect 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



Last Updated: 2018-08-01


next webcast button


next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, 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 serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

 Very helpful walkthrough instruction. It solved my problem


Thursday, August 02, 2018 - 3:38:11 AM - Onuoha Miriam Ozioma Back To Top

 Thanks for all the tips 


Learn more about SQL Server tools