How to Connect to LocalDB from SQL Server 2014 Express

installationsql serversql server 2014sql-server-localdb

I downloaded both the LocalDB and Management Studio for MS SQL Server 2014 Express so that I can practice on my local machine. However, when I open Management Studio and try to browse to localDB to connect to to start created databases I can't locate the localDB.

May someone please assist me with this?

Best Answer

SQL Server Express LocalDB (commonly referred to as just "LocalDB") is not a service-based application: it does not show up in "Services", nor does it start up when the machine starts or even stay running until manually shut-down.

LocalDB is user-mode background process. It starts either when the first request to connect comes in (assuming the request is using the "(localdb)\InstanceName" syntax) or when explicitly started via the SqlLocalDB.exe command-line utility.

So, if you know the instance name, just try to connect and the first time will take a moment while it starts up, but then subsequent connections (assuming consistent activity) will be quick.

If you don't know the name of the instance, you can get the list by using the SqlLocalDB.exe utility as follows:

C:\>SqlLocalDB i

That will return a list of instances that your Windows login has access to. If an instance name starts with .\ then it is a shared instance that other users can access as well (the default is that instances are private to the login that created them).

From there, you can get more info (including whether the LocalDB instance is "Running" or "Stopped") by doing:

C:\>SqlLocalDB i {InstanceName}

And you can start it by doing:

C:\>SqlLocalDB s {InstanceName}

Once the last connection to a LocalDB instance is closed, the instance will automatically stop after about 5 minutes (this is the default setting but can be configured to be longer). The delayed shutdown is to prevent needing to wait for the startup again for connections that come in shortly after the last connection ended.

LocalDB always has a "default" instance that is created when installing, even if you never use it. In SQL Server 2012 Express LocalDB, the default instance name is v11.0. In the 2014 version of LocalDB, the default instance name is MSSQLLocalDB. And, just as a period "." can be used to connect to a non-LocalDB default instance:

C:\>SQLCMD -S . -E -Q "SELECT @@SERVERNAME;"

it can also be used with the "(LocalDB)\InstanceName" syntax to connect to the default instance:

C:\>SQLCMD -S (LocalDB)\. -E -Q "SELECT @@SERVERNAME;"

Please note that you can only have one default instance of LocalDB specified as being the default instance. Meaning, if you have both the 2012 and 2014 versions of LocalDB installed, using (LocalDB)\. will refer to the MSSQLLocalDB instance and not the v11.0 instance.

Related Question