LocalDB (1/1)

Introduction

LocalDB [1] is an execution mode of SQL Server Express targeted to program developers. A good description of the differences between LocalDB and the other SQL Server versions can be found at MSDN Blogs: Introducing LocalDB, an improved SQL Express [2].

Installation

LocalDB is usually installed as part of Visual Studio. In this tutorial we will use LocalDB v11.0 (that corresponds to SQL Server 2012) which was installed with Visual Studio 2012.

However if you are only interested in LocalDB a minimal installation of LocalDB v11.0 can be performed by downloading and installing the following 3 packages from the Microsoft Download Center:

  1. Microsoft SQL Server 2012 Express: After clicking download you'll be given a list of several packages to choose from.
  2. Microsoft ODBC Driver 11 for SQL Server - Windows: This is a prerequisite for the next package.
  3. Microsoft Command Line Utilities 11 for SQL Server: The LocalDB package doesn't include the tools to query databases such as SQLCMD.EXE, this package installs these tools. If you already have the tools installed or simply have no need for the tools, you can skip this package and the ODBC driver.

Regardless of the installation method the LocalDB engine will be installed in C:\Program Files\Microsoft SQL Server\110\LocalDB\Binn (yes there are two n's at the end, it's not a typo). The utility to manage LocalDB will be installed as C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SqlLocalDB.exe.

SQLCMD.EXE can be found in C:\Program Files\Microsoft SQL Server\110\Tools\Binn (Visual Studio installation) or in C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn (separate tools package installation).

LocalDB Instances

We will now show how LocalDB can be managed using the SqlLocalDB.exe tool [3]. We will work with LocalDB as setup by Visual Studio 2012.

On a given server you can run multiple independent LocalDB "servers". Each of these "servers" is called an instance [4] of LocalDB. Each instance hosts several databases:

So from that point of view an instance is a logical grouping of databases.

Let's start with a simple example and get the list of LocalDB instances by using the info|i command. Note that this will not list all the LocalDB instances on the machine but only those that are owned by the current user and the shared LocalDB instances. The execution of this command is shown below.

Listing the LocalDB instances
c:\Program Files\Microsoft SQL Server\110\Tools\Binn>SqlLocalDB.exe i
Projects
v11.0

c:\Program Files\Microsoft SQL Server\110\Tools\Binn>

We can see that there are two instances: Projects and v11.0. The output on your system may be different of course.

Let us now create our own instance with the create|c command. The execution of this command is shown below.

Creating a new LocalDB instance
c:\Program Files\Microsoft SQL Server\110\Tools\Binn>SqlLocalDB.exe c ExampleDB1

LocalDB instance "ExampleDB1" created with version 11.0.

c:\Program Files\Microsoft SQL Server\110\Tools\Binn>

We can now run SqlLocalDB.exe i again to get the list of instances. It now contains the "ExampleDB1" instance we just created. We can also run SqlLocalDB.exe i ExampleDB1 to get detailed information about the "ExampleDB1" instance. The execution of both commands is shown below.

Getting information about the new LocalDB instance
c:\Program Files\Microsoft SQL Server\110\Tools\Binn>SqlLocalDB.exe i
ExampleDB1
Projects
v11.0

c:\Program Files\Microsoft SQL Server\110\Tools\Binn>SqlLocalDB.exe i ExampleDB1

Name:               ExampleDB1
Version:            11.0.3000.0
Shared name:
Owner:              Pathfinder\Xavier Leclercq
Auto-create:        No
State:              Stopped
Last start time:    31/10/2014 20:51:52
Instance pipe name:

c:\Program Files\Microsoft SQL Server\110\Tools\Binn>

LocalDB instances are stored in the AppData folder in the user profile [5]. For instance in my case this is C:\Users\Xavier Leclercq\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances. If you navigate to this folder you should see a folder for each of the instances listed by the SqlLocalDB.exe i command. We will look into the contents of these folders later when we list the databases in the instance.

Newly created instances are in a stopped state. You can start and stop an instance using the start|s and stop|p commands as shown below. Note that it is not necessary to do this explicitly. As part of the principle that LocalDB should be easy to manage, instances are automatically started when a client connects to them and shut down when not in use.

Starting and stopping a LocalDB instance
c:\Program Files\Microsoft SQL Server\110\Tools\Binn>SqlLocalDB.exe s ExampleDB1

LocalDB instance "ExampleDB1" started.

c:\Program Files\Microsoft SQL Server\110\Tools\Binn>SqlLocalDB.exe p ExampleDB1

LocalDB instance "ExampleDB1" stopped.

c:\Program Files\Microsoft SQL Server\110\Tools\Binn>

Databases

Now that we have created an instance we can use the SQLCMD.EXE tool [6] to connect to it. The -S option is used to specify which instance we want to connect to. It takes the form [protocol:]server[\instance_name][,port]. For LocalDB the server should be "(localdb)" and since in our example we want to connect to the "ExampleDB1" instance we use "(localdb)\ExampleDB1".

Note that if you are following the tutorial step-by-step the "ExampleDB1" is now in a stopped state. Connecting to it will automatically start it. We can show this by running SqlLocalDB.exe i ExampleDB1 afterwards and check that the instance is now in "Running" state.

The execution of these commands is shown below. We connect to the instance with SQLCMD.EXE and then exit immediately afterwards (we will do more in our next example).

Connecting to the new LocalDB instance
c:\Program Files\Microsoft SQL Server\110\Tools\Binn>SQLCMD.EXE -S (localdb)\ExampleDB1
1> exit

c:\Program Files\Microsoft SQL Server\110\Tools\Binn>SqlLocalDB.exe i ExampleDB1

Name:               ExampleDB1
Version:            11.0.3000.0
Shared name:
Owner:              Pathfinder\Xavier Leclercq
Auto-create:        No
State:              Running
Last start time:    31/10/2014 20:58:31
Instance pipe name: np:\\.\pipe\LOCALDB#DD1DF493\tsql\query

c:\Program Files\Microsoft SQL Server\110\Tools\Binn>

We will now show some T-SQL [7] commands to get the list of databases in the instance, to create a new database and to delete a database. The goal of this tutorial is not to teach you T-SQL but how to use LocalDB so we won't explain the commands in details. If you want to learn more about T-SQL you can read our T-SQL tutorial.

The output of the T-SQL commands will often exceed the default width of the console. You can change it by using the command mode <columns>,<rows> e.g. mode 150,50. If you don't do that lines will wrap and the output will become very hard to read

The list of databases in the instance is kept in the master.dbo.sysdatabases table of the master database [8] which records all the system-level information for a SQL Server system.

A simple SELECT statement can be used to get the list of databases as shown below.

Listing the databases
c:\Program Files\Microsoft SQL Server\110\Tools\Binn>SQLCMD.EXE -S (localdb)\ExampleDB1
1> SELECT name,dbid FROM master.dbo.sysdatabases;
2> GO
name                                                                                                       dbid
---------------------------------------------------------------------------------------------------------- ------
master                                                                                                          1
tempdb                                                                                                          2
model                                                                                                           3
msdb                                                                                                            4

(4 rows affected)
1>

Although we haven't created any database yet there are 4 databases in the list. They are the system databases described in MSDN: System Databases [9]. Note that there are actually 5 system databases but one of them, the Resource database, is hidden [10].

Earlier in this tutorial we showed that the instances are stored on disk in the AppData folder [5]. If we have a look in the folder for the ExampleDB1 instance, which in my case is C:\Users\Xavier Leclercq\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\ExampleDB1, we should see 4 database files corresponding to the 4 system databases: master.mdf, model.mdf, msdbdata.mdf and tempdb.mdf. Again you can see that the Resource database is special as it doesn't appear in this list.

Let's now create a new database. This can easily be done with the CREATE DATABASE statement [11] as shown below.

Creating a new database
c:\Program Files\Microsoft SQL Server\110\Tools\Binn>SQLCMD.EXE -S (localdb)\ExampleDB1
1> CREATE DATABASE mydatabase1;
2> GO
1> SELECT name,dbid FROM master.dbo.sysdatabases;
2> GO
name                                                                                                       dbid
---------------------------------------------------------------------------------------------------------- ------
master                                                                                                     1
tempdb                                                                                                     2
model                                                                                                      3
msdb                                                                                                       4
mydatabase1                                                                                                5

(5 rows affected)
1>

By default new databases are stored in the root of the user profile [5] so in my case that is C:\Users\Xavier Leclercq. If you navigate there you'll see the mydatabase1.mdf and mydatabase1_log.ldf files of our new database.

Many people will want to store the databases in another location. This can be achieved by specifying additional parameters to the CREATE DATABASE statement as shown below.

Creating a new database in a specific location on disk
c:\Program Files\Microsoft SQL Server\110\Tools\Binn>SQLCMD.EXE -S (localdb)\ExampleDB1
1> CREATE DATABASE mydatabase2 ON (name='mydatabase2', filename='C:\MyDBs\mydatabase2.mdf');
2> GO
1> SELECT name,dbid FROM master.dbo.sysdatabases;
2> GO
name                                                                                                       dbid
---------------------------------------------------------------------------------------------------------- ------
master                                                                                                          1
tempdb                                                                                                          2
model                                                                                                           3
msdb                                                                                                            4
mydatabase1                                                                                                     5
mydatabase2                                                                                                     6

(6 rows affected)
1>

References

  1. MSDN: SQL Server 2014 Express LocalDB
  2. MSDN Blogs: Introducing LocalDB, an improved SQL Express
  3. MSDN: Command-Line Management Tool: SqlLocalDB.exe
  4. informIT: SQL Server Instances
  5. MSDN Blogs: LocalDB: Where is My Database?
  6. MSDN: sqlcmd Utility
  7. Wikipedia: Transact-SQL
  8. MSDN: master Database
  9. MSDN: System Databases
  10. Kalen Delaney: Geek City: The Resource Database
  11. MSDN: CREATE DATABASE (SQL Server Transact-SQL)

blog comments powered by Disqus

Copyright(c) 2006-2015 Xavier Leclercq | Privacy policy

Home
Contact Us
Search