LocalDB, or SQL Server Local DB to give it its full name, is a Microsoft product aimed at developers. It has been designed to act as an embedded database system and to replace SQL Server Express or SQL Server Development edition for most development uses.

LocalDB uses the same API as the full edition of SQL Server, about the only difference you'll notice is in the connection string you use to access it. All the client providers that work with SQL Server editions should also work with LocalDB with no changes to your code.

LocalDB, or SQL Server Local DB to give it its full name, is a Microsoft product aimed at developers. It has been designed to act as an embedded database system and to replace SQL Server Express or SQL Server Development edition for most development uses.

LocalDB uses the same API as the full edition of SQL Server, about the only difference you'll notice is in the connection string you use to access it. All the client providers that work with SQL Server editions should also work with LocalDB with no changes to your code.

The connection string used will specify the instance name to access and can include the specific database file to use.

Server=(localdb)\ProjectsV13; Integrated Security=true; AttachDbFileName=D:\Data\ADatabaseFile.mdf

The "AttachDbFileName" parameter can be left blank and the standard Catalogue keyword can be used to reference a Database by name.

The application and DLLs providing the engine install with a relatively small footprint into "C:\Program Files\Microsoft SQL Server\<version>\LocalDB\Binn\" where <version> is the major internal version number of the SQL server version (110, 120, 130).

The instances for LocalDB will be created by default in the folder "C:\Users\<username>\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\". As this path implies, LocalDB instances are, by default, user specific instances.

Inside the "Instances" folder you will find named instance folders. Inside these instance folders is where you will find the MDF and LDF files. There may already be several instance folders and they may have various names both depending on the versions of LocalDB you have installed and what other products you may be using. For example if you install the SQL Server Data Tools for Visual Studio 2015 and create a web application project using a database, then you will end up with a folder named ProjectsV13. If there is some other application using a default instance you may have a folder called LocalDB or MSSQLLocalDB.

It doesn't really matter what the names of these default instances are called as long as you know where they are and what they are called. You can check this by visiting your user instances folder.

One of the interesting features of LocalDB is that it will autostart instances as needed and even create instances automatically if they are requested and don't exist.

Microsoft provide a tool called SqlLocalDb.exe to allow you to easily manage local instance folders. The SqlLocalDb.exe tool should be part of your command path so you should not need to provide the full path when using the tool.

The SqlLocalDb tool lets you start, stop, create, delete, and ask for information about an instance. So if there's a bit of a mess in your LocalDB user instances it's simple to clean up and start from scratch. Be careful to identify the reason each instance folder is there before you go destroying everything.

Say we have an instance called ProjectsV13 (a default name used by Visual Studio), we can remove this or recreate it simply by using the SqlLocalDb tool.

To remove an instance, you use the command keyword "delete" and pass it the instance name. You don't need to give it the full path to the instance, just the instance name:

C:\>sqllocaldb delete ProjectsV13

If the instance doesn't exist (possibly because you typed the name incorrectly or it needed to be quoted due to a space), then you'll get the following error:

Delete of LocalDB instance "ProjectsV13" failed because of the following error:
The specified LocalDB instance does not exist.

If the instance is in use for some reason, then you'll get an error instead of the instance being deleted:

Delete of LocalDB instance "ProjectsV13" 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.

To stop a running instance you use the command keyword "stop" and as with all the instance specific commands you pass it the instance name:

C:\>sqllocaldb stop ProjectsV13
LocalDB instance "ProjectsV13" stopped.

Once the instance is stopped, deleting it should now be possible:

C:\>sqllocaldb delete ProjectsV13
LocalDB instance "ProjectsV13" deleted.

To create a new instance you use the command keyword "create" and pass it the name of the instance you want to create; make sure to quote the name if you use any spaces in the name:

C:\>sqllocaldb create "My Local DBs"
LocalDB instance "My Local DBs" created with version 13.0.1601.5

For each of the instances you can request some basic information using the command keyword "info" and passing it the instance name:

C:\>sqllocaldb info "My Local DBs"
Name: My Local DBs
Version: 13.0.1601.5
Shared name:
Owner: <machine>\<user>
Auto-create: No
State: Stopped
Last start time: 31/10/2016 16:41:14
Instance pipe name:


 In the information above you may notice that there is a "Shared name" property. This is used to create a shared instance of LocalDB. A user account with local administration privileges can make a user instance into a shared instance and also make a shared instance revert back into a user instance.

To make a shared instance, you specify the "share" command keyword and specify the user instance name and give it the new shared instance name:

C:\>sqllocaldb share "My Local DBs" "My Shared DBs"
Private LocalDB instance "My Local DBs" shared with the shared name: "My Shared DBs".


 If we now run the info command again we can see our shared name:

C:\>sqllocaldb info "My Local DBs"
Name: My Local DBs
Version: 13.0.1601.5
Shared name: My Shared DBs
Owner: <machine>\<user>
Auto-create: No
State: Stopped
Last start time: 31/10/2016 16:41:14
Instance pipe name:



You'll need to change your connection string if you're using a shared instance. The name "(localdb)" needs to be appended with the standard local server naming of ".\":

Server=(localdb)\.\ProjectsV13; Integrated Security=true; AttachDbFileName=D:\Data\ADatabaseFile.mdf


So why use LocalDB instead of SQL Server Express?

• Partly because you don't have to worry about managing an installation of SQL Server.
  
  
• The installation is simpler than a full blown SQL Server edition.
  
  
• Managing instances is far easier.
  
  
• It will auto create instances and databases for development projects created through Visual Studio.
  
  

There are some points to note when using LocalDB.

• For users to access shared instances it may require users to be explicitly given access to the shared instance folder and any database files used by that instance. In some circumstances the virtual folder redirection in Windows may get in the way. LocalDB is not designed to be owned by system accounts such as NT AUTHORITY\SYSTEM and all instances should be created by normal user accounts.
  
  
• Using LocalDB with versions of dotNet prior to 4.02 can be awkward and is not well supported. We highly recommend only using LocalDB if you are using dotNet versions 4.02 and above.
  
  
• Some features of the full SQL Server editions are not supported. To the best of our knowledge these include merge replication, FILESTREAM, and remote service broker queues.