Developers using SQL Server Express face a few challenges in their day to day work. One is that setting up and maintaining Express can be a daunting task. Another is that using the "User Instances" feature (which has been deprecated) leads to a lot of confusion - developers connect to one instance of the database through their tools, and another instance of the database through their program, and don't understand why updates to one aren't reflected in the other. This is actually due to the AttachDbFileName setting, but in most examples I've seen, the two seem to go hand in hand.
SQL Server 2012 introduces a new feature, SQL Express LocalDB. The purpose of this new feature is to provide developers with a local environment that is much easier to install and manage. Instead of installing a service and configuring security, they just start up an instance of this new LocalDB runtime as needed. In addition, the download for the SQL Express LocalDB runtime is only about 33 MB (or 27 MB, if you are still on x86), compared to the 100s of MBs required to download a full edition of SQL Server Express.
Before you get started, you'll want to make sure that your operating system is patched to the latest service pack and current according to Windows Update. Supported operating systems are as follows:
If you try to just delete the instance without first stopping it, you will get this error:
Delete of LocalDB instance "MyInstance" 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 check on the status and other details about an instance, you can run:
Now that an instance is created and started, you'll probably want to do other things like create databases and run queries. Unfortunately SqlLocalDb on its own only provides an interface to the engine; it does not provide a means to interact with databases. However there are several other ways to connect to and interact with SqlLocalDb instances.
In order to use sqlcmd, you must install either SQL Server 2012 Management Studio Express or the client tools from a regular SQL Server 2012 edition (though I caution against using Evaluation Edition here, since the client tools will expire after 180 days). You can install just the SQL Server 2012 Command Line Utilities, but I will install Management Studio Express since it's free, supports all of the functionality you should need to manage LocalDB instances, and for most tasks is preferable to sqlcmd anyway. You can download Management Studio Express from this page:
As with SqlLocalDb, you'll want to pick the file that is appropriate for you, depending on whether you are running on x86 or x64. When the Installation Center opens, you'll want to pick the option "New SQL Server stand-alone installation or add features to an existing installation" - even though neither describes what you're really doing.
On the Product Updates screen, you can uncheck the box to prevent it from checking the web for updates (or to prevent it from timing out in the event you have slow or no connectivity). At the time of writing no updates were found anyway.
While it should be checked by default, make sure that on the Feature Selection screen, "Management Tools - Basic" is selected.
Once installed, you can connect to the local instance using sqlcmd. Be sure to use the most recent version of sqlcmd on your system; if you have multiple versions (e.g. from previous versions of SQL Server or Visual Studio), calling sqlcmd will pick up the first one in your PATH environment variable, which is almost certainly going to be the earlier version. You can create a shortcut to cmd setting the start location to:
So now you can create a database, create a table, etc.
1> CREATE DATABASE foo;
1> USE foo;
Changed database context to 'foo'.
1> CREATE TABLE dbo.bar(ID INT);
Management Studio / Management Studio Express
We can connect to this instance using Management Studio Express in a similar way. You can launch ssms.exe from the cmd line, and when prompted for a connection, use the server name "(localdb)\MyInstance":
Here is the database / table we created through sqlcmd (we could of course continue adding/editing objects from Management Studio):
One thing you'll want to make note of is the location of databases in a SqlLocalDb instance. As described in this blog post, databases are by default created in your user profile directory. So if your profile is on C:\ and that drive is short on space, or if you want to create your databases on other drives for whatever reason, you'll need to use explicit locations in your CREATE DATABASE statement, e.g. to put the data file on D:\ you can say:
CREATE DATABASE foo ON (name = 'foo_data', filename = 'D:\dev\foo_data.mdf');
It would be nice if you could set the default data / file paths for the SqlLocalDb instance so that you didn't have to specify the locations every time, but this screen is greyed out:
SQL Server Data Tools
In addition to using sqlcmd and Management Studio Express, you can download SQL Server Data Tools (SSDT), which also installs the Visual Studio 2010 Shell if you don't already have some edition of Visual Studio 2010 installed. You can download SSDT from the following site:
It took me a little finagling to get PowerShell to connect to my LocalDB instance. Even with SQL Server 2012 client tools and the .NET 4.0.2 update installed, it seems that the version of SMO that PowerShell uses an older version of SMO that is not LocalDB-aware. Granted, this was Windows 7 without any explicit updates to PowerShell. While you should be able to connect using this syntax:
Various attempts at server names (such as (localdb)\.\MyInstance and .\MyInstance yielded generic "Failed to connect to server" errors. It turns out I had to connect using the named pipe syntax, which was necessary for other applications prior to the 4.0.2 update:
To determine that little hex code associated with the named pipe for the selected instance, you can use the SqlLocalDb info command. As you can see in the following screen shot, I just copied the output from the "Instance pipe name" line:
Note that this hex code will change every time you start the instance. Hopefully this issue will be fixed soon so that PowerShell can connect to SqlLocalDb instances just like the other applications can.
In order to connect via ADO.NET, there is only a minor change in the way you specify your connection string. As per the above examples, you just use (localdb) in place of "." or "machine name":
Data Source=(LocalDB)\MyInstance;Initial Catalog=foo;Integrated Security=True;
This requires the .NET Framework 4.0.2 update, however, otherwise you'll have the same issues I disclosed about PowerShell, and you'll need to use the LOCALDB#HEX-PIPE connection format.
Hopefully this gives you a jump start in using SQL Server 2012 Express LocalDB for local development. There are other aspects I haven't touched on, that I may treat in future tips - for example, sharing instances with other users, and the fact that LocalDB is named as such for a reason: it does not accept remote connections.
Download and install SQL Server 2012 Express LocalDB.
Use this standalone engine for new local development or prototype work.
i am very interested to see if/how LocalDb can be useful to our organization. my biggest problem is that i dont see any 'errorlog' file thats associated with LocalDb.
for troubleshooting purposes, sqlexpress has a \Log directory and an active 'errorlog' file where bad login attempts, informational status, errors related to trying to create database backups and/or restores are also logged.
i would think such things would also be useful to know for a LocalDb instance.
do you have any insights into this?
also have you discovered any way to get LocalDB, and matching cli utility, to work on Windows XP? ive been successful to install AND USE SUCCESSFULLY the sql2012 'sqlncli11' client connectivity library on Windows XP 32 bit. ive also been succesful to install AND USE SUCCESSFULLY the sql2012 'sqlcmd' on Windows XP 32 bit. Ive also been able to install LocalDB onto Windows XP 32-bit, however it unfortunately does not run (as there is a dependency for dlls that ship with Windows Vista or Seven - which obviously, are not normally found in Windows XP.
Wednesday, May 23, 2012 - 6:23:45 PM - Aaron Bertrand
There is certainly an error log for LocalDB instances. You can see this in Management Studio just like any other SQL Server instance: Management > SQL Server Logs. If you want to locate the files directly, you have to dig a little deeper; they're stored in the same folder where databases are created by default:
C:\Users\\AppData\Local\Microsoft Microsoft SQL Server Local DB\Instances\
The current error log is simply called error.log.
As for Windows XP, sorry, I haven't really looked into that, since that OS is so old (and I doubt you should expect any official support from Microsoft). You're probably safer on those machines to just stick with normal SQL Server Express.
Wednesday, May 23, 2012 - 6:24:52 PM - Aaron Bertrand
Whoa, I have no idea what happened to the formatting on that reply, sorry. The current error log is in:
C:\Users\[your user name]\AppData\Local\MicrosoftMicrosoft SQL Server Local DB\Instances\[instance name]\error.log
thanks tons. so that takes care of my first main concern...
perhaps i could ask you one more... my second concern is that all documentation states that in order to install LocalDB the user must be a member of the (local?) Administrators group. i do realize LocalDb was primarily developed for 'developers' to use - and this would be understood that most application developers (eg; people who understand very well computing) would be (local?) Administrators on their own machines.
but if the aimpoint i have is a lighter weight, easier to install, easier to support database engine to be used by 'end users' (of an sql based application) - this is a different scenario. each potential 'end user' could have different user account situations. some could indeed be (local?) Administrators, and others could be domain users in a large organization where their IT management team FORBIDS the average user from having (local?) Administrator priveleges.
ideally - in my opinion - LocalDb 2012 is a 'step in the right direction'. but i see a next release being ideally suited for 'end user' usage to add a new design aimpoint - remove the requirement of being an Administrator to install LocalDb. If this restriction can be removed, then perhaps a future LocalDb 'user instance' could be freely and easily installed by a 'standard' windows user. this would make sql based application (for 'stand alone' deployment) be much easier.
but since we are not there yet, and/or perhaps Microsoft's thought about above idea is not a match to mine, that leaves us with the present 'hard-requirement' of needing to be an Administrator to successfully install LocalDb 2012. i believe LocalDb installs as a *.msi file. do you know of any way the following can be robustly achieved?
a 'standard windows user' launches an install of LocalDb - and it is performed in a manner where it installs properly. in other words, is there a way that a standard windows user can somehow force the installation to run elevated as Administrator, if they are not a member of the (local?) Administrators group?
ive read one may start 'msiexec' using '/a' parameter which somehow aims to do this, but it doesnt state if the user running such a command must be a member of Administrator group to effectively use the parameter.
i also know one may 'elevate a cmd window' to run as Administrator, the user may then cd into the directory that contains the *.msi file, then the user may invoke the full file name of the *msi installer. this is far too complex of a sequence to expect computer non-savy end users to follow merely to install a program. certainly there must be an 'easier' way...
Thursday, May 24, 2012 - 12:04:46 PM - Aaron Bertrand
It is not perfect. To install LocalDB you may have to be an administrator (I'm not sure, I don't work in an environment where developers' machines are that locked down). But once it is installed, you shouldn't have to be an administrator to create and use instances.
Thursday, May 24, 2012 - 12:40:22 PM - Aaron Bertrand
And a further thought - if your machines are locked down due to group policy, corporate rules, what have you - why is it a good idea to bypass that for SqlLocalDb? If the policy dictates that they shouldn't be installing software on their local machines, then they shouldn't be installing software on their local machines, and I'll suggest the same thing I've suggested elsewhere: maybe local development isn't the answer. They can do the same thing connecting to their own database on a SQL Server instance you manage elsewhere.
my day-to-day role is 'support' for a software company who sells sql based applications. im constantly working with both end users and IT support staff to install/configure/troubleshoot the software my employer produces.
im used to being, at times, almost verbally assaulted by IT people (without any further qualification being made here...) who sometimes say to me 'why is your software so hard to install, i dont want to give person such and such admin priveleges to install your software with, blah, blah, blah, etc...'
up till now, ive always had sort of an 'im the undergog' in this type of conversation. your last statement (above) has given me a totally different perspective on this. next time some IT person comes at me with this type of attitude, ill just respond with 'well its YOUR policy to not allow joe to install software he needs to use to do his job, that requires you work with me to install it! so lets work together in a cherry fashion and be done with it.'
thanks for inspiring me to change my tact here.
Thursday, May 24, 2012 - 3:38:25 PM - Aaron Bertrand
Thanks for all descriptions. But there is an error i cannot fix...
I've already used the localdb on a small project. I tested and saw no problem.
But when i deployed the application some users complained about connection problems which says there is a network related error to connect (localdb)\v11.0 instance is not reachable.
I finally reproduced the error message. The setup is ok, the .Net 4.0.2 update is ok and all other Windows updates are ok. There is no error when the application is started with Administrator account.
But when a user account is used the exception throws and says that the sql server is not reachable. Do you have any idea for the customers who wants to use a username which have no administrator privileges on their computer.
Wednesday, May 30, 2012 - 8:52:16 AM - Aaron Bertrand
A.Kadir, did you share the instance using sqllocaldb -share? Did you try having the non-admin user create their own instance (why do they need to use an instance the admin created instead of just creating their own)? Please see this StackOverflow question and all the comments: http://stackoverflow.com/questions/10214688/why-cant-i-connect-to-a-sql-server-2012-localdb-shared-instance
Wednesday, May 30, 2012 - 12:02:23 PM - greg aiken
i think that what A.Kadir reports here - is what i saw as a potential deployment concern. the same concern is being phrased in two different ways.
if it is possible for a (non-administrator) 'user' to create their own 'instance' of localdb by running "sqllocaldb create MyInstance", my best guess here would be that ideally this sequence should auto-magically create a windows authenticated login for 'user' to that newly created 'instance'. if this is indeed possible, that would be a very workable solution.
in an application deployment scenario this could be achieved by either instructing the 'end user' to cd into the '...Microsoft SQL Server\110\Tools\Binn' folder and then run "sqllocaldb create MyInstance"...
supply them with a script that does this for them.
if, on the other hand, even if a 'user' is able to create their own localdb instance - but this instance is unable to allow that 'user' to log in using windows authentication, then the only other route here would be to have an administrative account user both install localdb, and then sqlcmd (version 2012) and then;
create login localappuser with password="password", default_database=[master], default_language=[us_english], check_expiration=off, check_policy=off
(above could also be deployed as a script, albeit one that might need to be ran by the administrator)
the above sql essentially grants the standard 'user' full permissions within the realm of what one can do within localdb. of course if one knew explicit priveleges to be granted, as opposed to giving the user 'sysadmin' priveleges - that can also be done.
what this does is change how the user will loginto localdb. instead of logging in using windows authentication, this would allow them to get in using sql authentication (meaning in the connect string, one would use 'User Id=localappuser;Password=password;Persist Security Info=True;' (relative to the windows authentication connect string parameter 'Integrated Security=SSPI')
Thank you for the instructions. I am very new to all this and got stuck at the beginning. Successfully completed the SQL 2012 express LocalDB installation, but having problems interacting with it using the command line.
C:\> SqlLocalDb infogives the following error message:
SqlLoacalDb not recognized as an internal or ……..
I have window 7, office 2010 (32 bit) on a 64 machine and successfully followed all your instructions up to the error message.
Thursday, August 23, 2012 - 9:19:54 AM - Aaron Bertrand
Quick question if anyone knows - I'm using the instance pipe name to connect which works however when I execute something like SELECT @@SERVERNAME I find that it returns a name with similar hex bits included - this is fine however I need to have a constant instance name instead of the different one (with it changing between restarts of the instance)...i.e. to use as something that can always be connected to from a config file or for storing the name within the database for use somewhere else.
Thursday, September 06, 2012 - 9:42:49 AM - Aaron Bertrand
@Vix, you shouldn't have to continue using the instance pipe name to connect. Are you using an older driver that doesn't understand localdb? Can you describe what application requires the instance pipe name to be used?
Yeah we're connecting from .NET 2.0 applications still and we don't really have the option to upgrade just yet. I've tried connecting to the instance using the methods described but perhaps I need to share the instance first or something? I'm guessing the pipe name is the only one that is unique/different everytime however the bit that scared me is that when you execute select @@ServerName or select ServerProperty('ServerName') then it returns the machine name together with the strange instanced hex bit:
MYCOMPUTERNAME\LOCALDB#8512366D The named pipe for the above connection was as follows: np:\\.\pipe\LOCALDB#8512366D\tsql\query
Surely if (LocalDB)\TestInstance worked then it should also return that as the name from @@ServerName?
Hello, I have a problem in that my Dell computer mis-reports the sector size of my new Advanced Format Harddrive as having a 3072 sector size, which causes LocalDB to choke. I tried installing a secondary drive and moving everything over to that, but there seems to be no way to convince LocalDB to accept that; I even see in your screenshots above that the "feature directories" are greyed out, just as they were in my install. How can I move LocalDB over to that other drive so that I can get it working on my developer computer? I'd hate to have to fall back to a virtual machine just because of that one annoying aspect of the installer. Isn't there some way to move the master db over to another drive or something?
Thursday, October 11, 2012 - 10:40:56 AM - Maheshraj
This was a great work by you, for so may days i was striving for a localdb , and u have made it real .
I have some problems while retsoring databases in my local. and following are the details while restoring the database into my lcoal.
I Installed LocalDb in my system and named the instance as (localdb)\MyInstance , i am trying to restore a database into the Localdb\MyInstance , but i am getting the error as "Property MasterDBPath is not available for Information 'Microsoft.SqlServer.Management.Smo.Information'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (SqlManagerUI)".
Please help me in restoring the database into my instance.
Thursday, October 11, 2012 - 7:02:02 PM - Aaron Bertrand
Hi Aaron ,Thanks for your valuable help, i followed the Key suggested by you , I restored the database into my (localdb\MyInstance) using Query window,
but i got an another Big problem .
i have installed a product build in my system and tried to run the schema using database configuration wizard , i selected the Dataserver as (localdb)\MyInstance and authentication as windows authentication , and clicked on next button .
I am getting the error as "a network -related or instance-specific error occurred while establishing a connection to sql server . The server was not found or was not accessible . verify that the instance name is correct and that sql server is configured to allow remote conections .(provider : sql network interfaces , error :26 -error locating server/instance specified) in server localdb\myinstance"
After that i went into sql server configuration manager and checked the SQL Server Services , but there are no item in the services.
Please help me in running the schema.
Friday, October 12, 2012 - 3:41:26 PM - Aaron Bertrand
Maheshraj sorry, but from where I'm sitting I'm going to have a very hard time troubleshooting your issue. You need to make sure the instance is started (it won't be listed in SQL Server Configuration Manager but you can get the list of instances using SqlLocaldb.exe info and then for any instance you can get details, including state, using SqlLocaldb.exe info "instance name").
Saturday, October 13, 2012 - 7:32:58 AM - Nitish Singh
can one add username and password in the connection string for localdb...I have tried doing that but some kind of error is there...i have checked and re-checked my userID and password...But nothing is happening....Can you guide me in right direction
Saturday, October 13, 2012 - 7:31:07 PM - Aaron Bertrand
This post provides great information, presented expertly, and I admire the effort you've gone to in your comments section to help others work with and get value from the new LocalDb. However, and someone's got to say it, aren't we getting beyond the scope of LocalDb? I'm not trying to be an elitest or purist or anything but LocalDb isn't really meant to be a SQLite competitor is it? I'm not just trying to be a jerk here. I really want to know your opinion on if you think LocalDb will become an alternative to SQLite and other such low impact Sql providers (even though I still might argue it's not designed to be)?
Thursday, November 29, 2012 - 11:06:26 AM - Aaron Bertrand
Brett, I suppose that deep down, aren't all products developed to fill a void and/or be better than their competitors? That said, I don't think SQLite users are the target audience in this case, even though some may find LocalDb more appealing than what they're using now (and full-on SQL Server Express / Developer).
LocalDb was developed - at least as far as I have been told - for two main purposes:
(1) to make a SQL Server developer's life easier. No need to install, configure and manage an instance of SQL Server. Get the runtime when you need it, without the overhead of a service when you don't.
(2) to make a workplace more secure. No need to manage security or remote connections, and no worry that an instance of SQL Server is forgotten about but running forever, unpatched, and exposed to who knows what over time.
While it's technically *supported* in production, that isn't its focus and it is not intending to unseat any of the other players there. Again, AFAIK. I don't work for Microsoft and so I don't have insight into all of their reasoning or intentions.
Thursday, November 29, 2012 - 1:05:21 PM - Brett Baggott
I created the "foo" database with "bar" table that have column called "ID" of data type int. My questions is how can I enter the actual table value? I want you to show me how to enter values 10, 30, ... into the actual data table dbo.bar.
Monday, December 03, 2012 - 7:32:44 AM - Jeremy Kadlec
Hi Aaron Thank you for a very well presented lesson on creating an instant. I am new to sql and have thus far only developed MSaccess and Visual basic desk top applications. I trying to move my back end tables to sql Server. Honestly speaking i simply did not understand that you must create and instance. I assumed sql server would automatically create one on installion. For days i could not access SQL. A hearty thank you for showing me the light. I intend to test my application locally and then export the sql database to a remote online server. May i ask if you could clarify the basic steps to achieve this. Thanks again
Thursday, December 20, 2012 - 4:50:00 PM - Aaron Bertrand
Hi Aaron, Thanks for the prompt reply. I have a MSACESS backend (ACCDB). Do you mean i should import into SQL server, backup and restore on the remote web hosting company and relink? Also what is the best way to import my database into my sql instant? Thanks again!
Friday, December 21, 2012 - 7:52:16 AM - Aaron Bertrand
I have tried this today, but I keep getting access denied when I try to use sqlcmd with my instance to create a DB. Management Studio gives a "file activation error" when trying to create the database.
I have permissions to read and write to "C:\Users\NAME\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MyInstance", why is this happening?
I have no prior experience with DBs, this is for a college assignment...
I am very new to SQL server 2012, I have a sql file named "Ranked_Key_Search.sql" . I double clicked it to open but some one said that I need to add my database after opening that particular file. Will you please explain me te procedure to add the database by using "restore databse" commend by right clicking. please reply soon, its very urgent