Microsoft SQL Server Express LocalDB is intended for developers, it is very easy to install and doesn’t require any complex configuration task to create an instance or to use the database. The Introduction on the installation of Local DB be found on “How to install Microsoft SQL Server Express LocalDB” article.
This article will show different ways of connecting to LocalDB and how to create and use an instance of LocalDB.
LocalDB runs on demand, which means that the LocalDB processes can start and be stopped automatically when needed. In practice, this will happen when a computer is started and there is no LocalDB process running. So, there are no resources dedicated to servicing a database server during one’s daily work routine. When an application that uses Microsoft SQL Server Express LocalDB is run and a connection is made to the database, the LocalDB process is started on the computer aka on-demand. When the last connection from the application is closed, after a short delay, then the LocalDB process ends.
Also, the attached database file name property can be used for the database connection string. In this way, the location of the database file can be controlled. The database file doesn’t have to be searched in some centralized SQL Server location, but it can be found in user’s Application Install folder. This means that the work is only being done with a database file, not with configuring and using a database server.
Once Microsoft SQL Server Express LocalDB is installed, using SqlLocalDB Utility can be seen as information about the LocalDB instances installed on the machine. By typing the SqlLocalDb info command and pressing the Enter key from the keyboard in the Command Prompt window, the following instances may be found:
The v11.0 and MSSQLLocalDB instances are automatic Microsoft SQL Server Express LocalDB instances and come with installation of Microsoft SQL Server Express LocalDB.
Depending on which version of Microsoft SQL Server Express LocalDB is installed on the machine, the different automatic LocalDB instance will appear. For example, if only Microsoft SQL Server 2012 Express LocalDB is installed, when the available LocalDB instances on a machine using the SqlLocalDb info command are listed, the v11.0 LocalDB instance will appear. But, if Microsoft SQL Server 2014 Express LocalDB or a newer one is installed, the MSSQLLocalDB instance will appear as the automatic instance.
Naming conventions of the automatic v11. 0 LocalDB instance can be taken from this link and naming conventions for the automatic MSSQLLocalDB LocalDB instance can be found on this link.
To see the other details about instances, for example v11.0 instance, type the following command:
SQLLocalDB info v11.0
The result will be as follows:
Name stands for the name of the LocalDB instance,
Version shows the full version of the LocalDB instance,
Shared name shares the instance using the specified shared name, when multiple users need to connect to a single instance of Microsoft SQL Server Express LocalDB.Type the following command: SQLLocalDB share v11.0 Mare in the Command Prompt window and execute. In the Command Prompt window will show the following message:
Private LocalDB instance “v11.0” shared with the shared name: “Mare”.
Make sure that the Command Prompt window is running under the administrator privileges, otherwise the following message may occur:
Sharing of LocalDB instance “v11.0” with shared name “Mare” failed because
Administrator privileges are required in order to execute this operation.
Now, when the SQLLocalDB info v11.0 command is executed, the result will be this one:
Owner shows the name of the Windows User (the owner of the LocalDB instance)
Auto-create indicates (shows) if LocalDB instance automatic is created or not. If the LocalDB instance is automatically created in the Auto-create section, Yes will appear. On the other hand, No will be shown.
Let’s create a new instance and demonstrate this. In the Command Prompt window, type the following command: SqlLocalDB create NewInstance and press the Enter key. The message will appear which indicates that the LocalDB instance is created:
LocalDB instance “NewInstance” created with version 13.0.2186.6.
Now, when the SqlLocalDB info NewInstance command is executed in the Auto-create section, No will be shown:
As it may be noticed, the version of the NewInstance LocalDB is different from the version of the v11.0 LocaDB instance. This happens, when on a machine, more than one version of Microsoft SQL Server Express LocalDB have been installed.
Now, when a new instance of LocalDB is created without specifying a version of Microsoft SQL Server Express LocalDB after a name of the LocalDB instance in the creating strings, by default, the higher version of the LocalDB will be used in creating the process of a new LocalDB instance.
Let’s create a new instance which will have the same version as the v11.0 LocalDB instance. Type the following command: SqlLocalDB create Test 11.0 in the Command Prompt window and press the Enter key. The following message will appear:
LocalDB instance “Test” created with version 11.0.2318.0.
Now, when the SqlLocalDB info Test command is executed, the result will be this one:
When creating a new LocalDB instance with the name which have two or more words speared by a space, for example Test instance, without putting them in the double quotation mark, the following message will appear:
Creation of LocalDB instance “Test” with version instance failed because of the following error
The parameter for the LocalDB Instance API method is incorrect. Consult the API documentation.
If executed the SQLLocalDB create “Test instance” command, the following message will appear in the Command Prompt window:
LocalDB instance “Test instance” created with version 11.0.
State shows a current state of a chosen LocalDB instance, if it is Running or it Stopped. To run, for example, the Test LocalDB instance, in the Command Prompt window, the following command should be typed: SqlLocalDB start Test. The result after starting the Test LocalDB instance is:
Now, the State is changed to “Running” and the Instance pipe name section has a value. This value (string) is used as the connection string to the Test LocalDB instance from another application.
Now, when an instance is created and started, it is not possible to do other things like creating databases and running queries since SqlLocalDb does not provide an interface to the engine by itself as it does not provide a means to interact with databases. Still, there are several other options to connect to and interact with SqlLocalDb instances.
For example, if the string from the Instance pipe name is pasted in the Server name field under the Connect to Server window of SQL Server Management Studio:
And the Connect button is pressed, the connection to the Test LocalDB instance will be established:
Now, when the connection is established, the same T-SQL code can be used in LocalDB as it is in SQL Server Express when creating databases, tables, stored procedures etc.
There are some restrictions related to LocalDB comparing to SQL Server Express, which can be seen here.
Another way to connect to the LocalDB instance is to type the (LocalDB)\ followed by the name of the LocalDB instance (v11.0) in the Server name combo box of the Connect to Server window and to click the Connect button:
In the Object Explorer window, the result will be like this:
If in the Server name combo box of the Connect to Server window is entered (LocalDB)\ followed by a dot ((LocalDB)\.):
The connection will be established with the automatic (default) LocalDB instance:
This type of connection to the automatic LocalDB instance refers to Microsoft SQL Server Express 2014 LocalDB or newer.
As it is mentioned in the text above, if more than one LocalDB versions are installed on the machine, the connection will be established with the automatic LocalDB instance with higher (latest) version.
Let’s delete that automatic LocalDB instance. In this situation, the MSSQLLocalDB instance is the one that should be deleted. In the Command Prompt window, the following command should be typed: SqlLocalDB delete MSSQLLocalDB. When the Enter key is pressed from the keyboard, the following message may occur:
Delete of LocalDB instance “MSSQLLocalDB” failed because of the following error:
Requested operation on LocalDB instance cannot be performed because specified instance is currently in use. Stop the instance and try again.
As the message says, the automatic MSSQLLocalDB instance is in use. In order to be deleted, first it should be stopped. Type the SqlLocalDB stop MSSQLLocaDB command in the Command Prompt window:
LocalDB instance “MSSQLLocalDB” stopped.
Now, repeat the SqlLocalDB delete MSSQLLocalDB command. This will successfully remove LocalDB instance from the machine:
LocalDB instance “MSSQLLocalDB” deleted.
Using sqlcmd, the connection with the LocalDB instance, can be established. From there, T-SQL command and various T-SQL queries can be used. In the Command Prompt window, navigate to the SqlLocalDB.exe: cd C:\Program Files\Microsoft SQL Server\110\Tools\Binn\
Also, type the following command: sqlcmd-S (localDB)\v11.0 in order to connect to v11.0 LocaDB instance.
When there is the connection to the v11.0 LocalDB instance, a database, a table, etc. can be created. Past the following query in the Command Prompt window:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE DATABASE TestSQlCMD; GO USE TestSQlCMD; GO CREATE TABLE dbo.Pesron( ID INT, Name VARCHAR(50) ); GO |
Now, let’s connect to the v11.0 LocaDB instance via SSMS:
As it can be seen, in the Object Explorer window, the TestSQlCMD database with the Pesron table was created in the v11.0 LocalDB instance:
Connecting to a shared Instance of LocalDB
To connect to a shared instance of LocalDB add .\ between (LocalDB)\ and a name of a shared instance. For example (LocalDB)\.\Mare:
If the .\ is omitted the following message will appear:
Previous article in this series
Reference
- SQL Server 2012 Express LocalDB
- SQL Server 2014 Express LocalDB
- LocalDB: Where is My Database?
- SQL Server Express LocalDB Instance API Reference
He is a prolific author of authoritative content related to SQL Server including a number of “platinum” articles (top 1% in terms of popularity and engagement). His writing covers a range of topics on MySQL and SQL Server including remote/linked servers, import/export, LocalDB, SSMS, and more.
In his part-time, Zivko likes basketball, foosball (table-soccer), and rock music.
See more about Marko at LinkedIn
View all posts by Marko Zivkovic
- How to connect to a remote MySQL server using SSL on Ubuntu - April 28, 2020
- How to install MySQL on Ubuntu - March 10, 2020
- Using SSH keys to connect to a remote MySQL Server - November 28, 2019