Getting Started with SQL Server 2012 Express LocalDB

By:   |   Updated: 2012-05-16   |   Comments (75)   |   Related: 1 | 2 | 3 | 4 | > Express Edition


Problem

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.

Solution

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.

Also, check out this related tip: Getting Started with SQL Server 2017 Express LocalDB.

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:

  • Windows 7
  • Windows Server 2008 R2
  • Windows Server 2008 Service Pack 2
  • Windows Vista Service Pack 2

It will also work on Windows 8 if you're using any of the pre-release versions, but I have not tested this on any of the Server Core variants of Windows Server, so you're on your own there. For further information on system requirements, please see Hardware and Software Requirements for Installing SQL Server 2012.

You'll want to make sure that you've installed .NET Framework 4.0 and, equally as importantly, the .NET Framework 4.0.2 update (KB #2544514).

Once your system is up to date, you can download the SqlLocalDb installer from:

http://www.microsoft.com/en-us/download/details.aspx?id=29062

Setup is rather trivial:

SQL Server 2012 introduces a new feature, SQL Express LocalDB
SQLLocalDB installer
Ready to Install
Installing SQL Server 2012 Express LocalDB
Completing the installation

Once installed, you can interact with SqlLocalDb using the command line. The following will tell you the version of SqlLocalDb:

C:\> SqlLocalDb info

Result:

v11.0

If you want to create an instance:

C:\> SqlLocalDb create "MyInstance"

Result:

LocalDB instance "MyInstance" created with version 11.0.

To start the instance:

C:\> SqlLocalDb start "MyInstance"

Result:

LocalDB instance "MyInstance" started.

You can also create an instance and start it in one command using the -s argument:

C:\> SqlLocalDb create "MyInstance" -s

To stop and delete an instance, you can issue two commands:

C:\> SqlLocalDb stop   "MyInstance"
C:\> SqlLocalDb delete "MyInstance"

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:

C:\> SqlLocalDb info "MyInstance"

Result:

Name:                MyInstance
Version:             11.0.2100.60
Shared name:
Owner:               METEORA\AaronBertrand
Auto-create:         No
State:               Running
Last start time:     4/29/2012 6:17:24 PM
Instance pipe name:  np:\\.\pipe\LOCALDB#ABB78D50\tsql\query

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.

sqlcmd

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:

http://www.microsoft.com/en-us/download/details.aspx?id=29062

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.

Management Studio Installation Center

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.

Product Updates screen

While it should be checked by default, make sure that on the Feature Selection screen, "Management Tools - Basic" is selected.

Feature Selection screen

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:

C:\Program Files\Microsoft SQL Server\110\Tools\Binn\

So now that sqlcmd is installed, and assuming you started an instance called "MyInstance" per above, you can connect using sqlcmd this way:

C:\Program Files\Microsoft SQL Server\110\Tools\Binn\> sqlcmd -S (localdb)\MyInstance
1> SELECT @@VERSION;
2> GO

Result:

sqlcmd result of SELECT @@VERSION

So now you can create a database, create a table, etc.

1> CREATE DATABASE foo;
2> GO
1> USE foo;
2> GO
Changed database context to 'foo'.
1> CREATE TABLE dbo.bar(ID INT);
2> GO
1> exit

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":

SSMS Connect to Server dialog

Here is the database / table we created through sqlcmd (we could of course continue adding/editing objects from Management Studio):

SSMS Object Explorer

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:

Server Properties dialog

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:

http://msdn.microsoft.com/en-us/data/hh297027

Eventually this will launch the Web Platform Installer:

Web Platform Installer
Web Platform Installer

Once you've installed SSDT, you can launch the program, create a new SQL Server Database Project, and use the new (localdb) instance it creates:

SQL Server Data Tools - Create Project

But you can also use "Add Server..." to connect to and work with your existing instance:

SQL Server Data Tools - Add Server
SQL Server Data Tools - Connect to Server
SQL Server Data Tools - Object Explorer

Visual Studio

To use SqlLocalDb with Visual Studio proper, see the following blog post, where Roel van Lisdonk walks you through using both Visual Studio 2010 and the newer VS11 beta to connect to SqlLocalDb:

http://www.roelvanlisdonk.nl/?p=2607

PowerShell

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:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
$sn = "(localdb)\MyInstance";
$srv = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList $sn;
$srv.ConnectionContext.LoginSecure = $true;
$srv.Databases | Select Name;

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:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
$sn = "np:\\.\pipe\LOCALDB#ABB78D50\tsql\query";
$srv = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList $sn;
$srv.ConnectionContext.LoginSecure = $true;
$srv.Databases | Select Name;

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:

PowerShell connectivity

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.

ADO.NET

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.

Conclusion

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.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2012-05-16

Comments For This Article




Tuesday, August 14, 2018 - 3:48:12 PM - Pallav Bhuyan Back To Top (77184)

Can't I simply copy paste the men file created in mysqlserver instance to another drive location and connect it to visual basic datasource


Wednesday, November 22, 2017 - 1:25:27 AM - Pargat Singh Back To Top (70072)

Wow, very helpful post. I really want this exact information. Thanks for sharing and giving your valuable time.


Wednesday, September 20, 2017 - 7:28:29 AM - Adam Foley Back To Top (66445)

Thanks for sharing this info.  This was exactly what I needed to get the SQL Server Managerment Studio working.  I tried looking through the MIcrosoft doco but was far too hard for my expertise.  Direct and to the point with great screen shots and notes.  Thanks again.  

 


Wednesday, May 31, 2017 - 1:03:07 PM - Aaron Bertrand Back To Top (56360)

@Rory It shouldn't have required a restart *unless* you were told during installation that a restart would be required (I sometimes see this on the setup dialogs, but not always, and it is not restricted to LocalDB).

 


Wednesday, May 31, 2017 - 12:17:46 PM - Rory Back To Top (56356)

 Very helpful. Thanks. I did have to restart my computer after installation, not mentioned. I guess that was understood?

 


Wednesday, November 16, 2016 - 12:50:00 PM - Saphiros Back To Top (43777)

  This was very helpful.. thanks


Monday, October 31, 2016 - 11:02:43 AM - azam Back To Top (43662)

 

 And now I want in sqlserver managment work with two tables ,one at localdb and other on my main server. For example I want insert some rows from one table of one database at localdb ,to another table of one database located on my main server.

How ? Please tell me the statemanet on sql SQL Server Management Studio.

I already connected to both of them.but transaction not yet.

 


Monday, October 31, 2016 - 10:49:01 AM - azam Back To Top (43660)

 

 Hi

It was very usefull.

thanks a lot.


Thursday, August 11, 2016 - 9:25:49 PM - BillS Back To Top (43108)

 Amazing post.  Thank you so much!  So nice to be up and running in ~5 minutes time.  


Tuesday, July 19, 2016 - 8:44:49 AM - Raj Back To Top (41918)

 

Yes. Its very usefull for me. Keep on post some new topics in SQL

 

Thank You

Raj. S


Friday, July 1, 2016 - 6:15:41 AM - JaneC Back To Top (41804)

we've just come across a requirement to install SQL Express 2012 LocalDB on a RDS host (terminal server)  -- is this possible or will we need to do something clever with App-V??

thanks

 


Friday, November 27, 2015 - 11:26:43 PM - Nagesh Back To Top (39150)

Very well laid out, helped be to set up SQL server on my personal desktop. Thanks a lot!!!


Friday, September 25, 2015 - 3:15:11 PM - crispe1586 Back To Top (38755)

Thank you very much, your article has been very very helpful!!


Saturday, August 29, 2015 - 2:58:56 AM - Johann Smit Back To Top (38564)

Thank you Aaron,

This is excellent. I started programming at retirement age (62). Had my own retail stores for approximately 50 years before the time.

It is now 10 years later and my software - focused on the meat and bakery trading areas - are doing well.

The database we use are MS Sequel - at present SQL Express 2012. Most of the programming is done in DotNet.

My major gray areas regarding the database are now well lit thanks to your article.

We are based Johannesburg - South Africa.

Regards,

Johann Smit

Trade Consultant and Software Developer.


Tuesday, July 28, 2015 - 4:38:18 PM - Steve Dell Back To Top (38302)

Just want to take the time and say thank you, for putting together this information.  It is well laid out, and very informative, and the links to items is ideal.  Thanks again!


Tuesday, March 17, 2015 - 5:27:48 AM - Haxsta Back To Top (36554)

Thanks works a treat, there is a newer version (SP2) http://www.microsoft.com/en-us/download/details.aspx?id=29062

 


Wednesday, January 7, 2015 - 2:11:22 PM - Aaron Bertrand Back To Top (35859)

Note that for SQL Server 2014 the syntax for creating an automatic instance has changed from (local)\v11.0 (many have tried (local)\v12.0 as well) to (local)\MSSQLLocalDB. See Connect #845278 for details:

http://connect.microsoft.com/SQLServer/feedback/details/845278


Monday, November 10, 2014 - 8:44:42 AM - Albert Back To Top (35240)

Hi Mr. Aaron,

 

I am new to SQL LOCALDB, previously I am using MS ACCESS for our local deployments. I am currently looking for a small database with small footprint for our deployment purpose.

Currently, I am stucked with this database LOCALDB, What I did is create a database to test the database will all the tables in it, until I tried to DETACT the database via MSVS2012 IDE. until now I cannot find a way to attach the LOCALDB DB.

based on my understanding, LOCALDB saves the mdf/ldf at the c:\ users\appdata etc etc folder, but what I did when I created my database is saved the database on a different location.

I tried the following, removed the instance on the MSVS IDE and RE-install the instance, Add server, add connection, stop and start the instance, all of them did not work.

 


Wednesday, October 29, 2014 - 3:56:37 AM - Ziba Back To Top (35105)

Hi, thank you so much for the guide and all the helpful information.


Saturday, October 4, 2014 - 7:12:13 AM - Shikar Back To Top (34835)
Hi Aaron
 
Thanks for this guide.
I have encountered the following errors:
 
C:\Program Files\Microsoft SQL Server\110\Tools\Binn\
'Program' is not recognized as an internal or external command, operable program or batch file.

C:\Program Files\Microsoft SQL Server\110\Tools\Binn\>
sqlcmd -S (localdb)\MyInstance 1> SELECT @@VERSION; 2> GO
Access is denied.
I am not sure what the issue is - please help?

Wednesday, October 1, 2014 - 6:20:03 AM - Waqar Ahmed Back To Top (34791)

Above link Really helped in solving my LocalDB Issue.

 


Tuesday, September 23, 2014 - 6:43:38 AM - prajakta Back To Top (34673)

i m using sql server management studio 2008 but i have error when i creating new table in database and i also cannot edit columns in tables. following error is occured:

what should i do, please reply me.

 

TITLE: Microsoft SQL Server Management Studio

------------------------------

 

This backend version is not supported to design database diagrams or tables. (MS Visual Database Tools)

 


Monday, September 1, 2014 - 8:13:21 AM - Aaron Bertrand Back To Top (34342)

Sorry Luiz, I've never seen that one, and I doubt the key-related message has anything to do with LocalDb. I am suggest you bring it up with the makers of the software package you're trying to install.


Saturday, August 30, 2014 - 6:40:43 PM - Luiz Arbore Back To Top (34335)

Hello,

I'm trying to install a software that starts by installing sql server 2012 express localdb when it reaches the updating part of installation it's interrupted by ""key not valid for use in specified state "" consequently cannot install the software. I have windows Vista 64 SP 2 all up to date. please help. Thank you


Thursday, March 20, 2014 - 9:08:19 AM - KUMAR UTKARSH Back To Top (29821)

Hello all,

 

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 


Monday, March 3, 2014 - 12:07:12 PM - Ashenafi Back To Top (29630)

You saved my day. Thanks buddy!

 


Monday, February 24, 2014 - 9:59:34 AM - cf_phil Back To Top (29553)

AAAAAAAAAAAAAH!!! Sweet RELIEF!!! AN ANSWER!!!!

My local engine was installed, but I couldnt login, or anything and was beginning to think I was going insane.

Turns out I needed to create a new instance on by local engine with the CMD.

Thanks very much for this article!

 

Today is now no longer a *complete* waste of time - just perhaps only 90% a waste of time now.

 


Friday, February 14, 2014 - 8:06:58 AM - Joel Back To Top (29450)

You Rock!  

This saved me an 2hrs of searching time


Friday, January 10, 2014 - 5:54:11 PM - OKie eko Wardoyo Back To Top (28020)

I'm new to sql server express.

my computer is Windows 8.1 64 bit, i have visual studio 2013 installed on my pc.

I tried to download what i need here http://www.microsoft.com/en-us/download/details.aspx?id=29062

First, i donwload and install ENU\x64\SQLEXPR_x64_ENU.exe then installed it, SUCCESS.

then, i donwload ENU\x64\SqlLocalDB.MSI and tried to installed. WOW, it said like below:

"there is a problem with this windows installer package. A DLL required for this installation to complete could not be run. Contact your suport personal or package vendor."

I don't know what it means.

thanks.


Thursday, August 22, 2013 - 12:06:55 PM - Den Back To Top (26435)

So... This is probably a stupid question.. but how do I make my server visible to Microsoft Access 2007? I went to create an ADP file with an existing server, but it doesn't show on the pulldown.  


Friday, July 5, 2013 - 10:34:34 AM - deepak gupta Back To Top (25727)

Dear Aaron,

I have two Servers for sql One is live and other is backup.

I created the autobackup sequences but want to do mirroring databases of both the servers in sql2012.

I need your kind help in getting this done.

warm regards.

deepak gupta


Monday, May 6, 2013 - 7:52:53 AM - Aizaz Ahmad Khan Durrani Back To Top (23734)

i have one problem in connecting to server so due to ur article my problem is solved .

 

thanks


Thursday, April 11, 2013 - 1:32:34 PM - Aaron Bertrand Back To Top (23309)

Thanks Jacob. Oh, and Windows 8 really still ships in x86? Why would anyone use that? :-)


Thursday, April 11, 2013 - 11:23:06 AM - Jacob Krimbel Back To Top (23307)

FYI, the default instance does not work on 32 bit Windows 8.  Easy steps to reproduce:

1.)  Install localdb on a Windows 8 x86 box

2.)  Connect to (localdb)\v11.0 using any application that can make a connection, such as SSMS.

3.)  Observe error message.

Only occurs with the default instance and only on Windows 8 x86.  Does not occur with named instances.


Tuesday, March 5, 2013 - 7:20:54 AM - Soma Tekumalla Back To Top (22568)

Very nice article - Glad I read it. I could NOT connect to my LocalDB instance using SQL Server 2005 Management Studio but was able to do so using SQL Servere 2012 Express. 


Monday, March 4, 2013 - 8:07:58 AM - AlexeyVG Back To Top (22547)

Can I use SQL Express LocalDB for commercial hosting services? According to the EULA - can not.

This restriction was in SQL Express 2005 before SP1.

And now back in 2012...


Friday, December 21, 2012 - 1:35:31 PM - Vitor Back To Top (21086)
Nevermind, turns out I had a previous sqlcmd even though it's the very first time I'm using SQL Server. It appears to install a lot of 2008 stuff for some reason.

Friday, December 21, 2012 - 1:17:26 PM - Vitor Back To Top (21084)
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... Thanks!

Friday, December 21, 2012 - 7:52:16 AM - Aaron Bertrand Back To Top (21077)

Sorry Colin, I haven't used MSAccess since Access 97, so I'm the last person you want to ask about that. :-)


Friday, December 21, 2012 - 3:00:23 AM - Colin Back To Top (21075)

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!


Thursday, December 20, 2012 - 4:50:00 PM - Aaron Bertrand Back To Top (21070)

Colin I think the easiest way would be to simply create a backup of your local database and send that to the remote server (I assume it's a web hosting company of some sort) for them to restore.


Thursday, December 20, 2012 - 3:37:31 PM - Colin Back To Top (21068)

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 - 11:28:14 AM - Vitor Back To Top (21060)
Nice and comprehensive article, thanks Mr. Bertrand.

Tuesday, December 11, 2012 - 7:46:10 AM - Mohankumar Back To Top (20871)

Thanks a lot.

Much useful article.


Monday, December 3, 2012 - 7:32:44 AM - Jeremy Kadlec Back To Top (20703)

Amanuel,

I would check out this tutorial - http://www.mssqltips.com/sqlservertutorial/2514/sql-server-insert-command/.

Thank you,
Jeremy Kadlec


Monday, December 3, 2012 - 6:52:17 AM - Amanuel Amente Back To Top (20701)

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. 


Thursday, November 29, 2012 - 3:48:28 PM - Aaron Bertrand Back To Top (20643)

Brett true, but maybe for different reasons than you think:

http://stackoverflow.com/questions/13571331/is-it-normal-to-use-localdb-in-production


Thursday, November 29, 2012 - 1:05:21 PM - Brett Baggott Back To Top (20638)

Well, that was my point, it's not really meant for production.


Thursday, November 29, 2012 - 11:06:26 AM - Aaron Bertrand Back To Top (20632)

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.


Wednesday, November 28, 2012 - 10:59:55 PM - Brett Baggott Back To Top (20617)

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)?


Saturday, October 13, 2012 - 7:31:07 PM - Aaron Bertrand Back To Top (19911)

Nitish, I don't know how to fix "some kind of error" - can you please post your question (including the actual error message) to th Q & A section where I'm sure someone will be able to help you:

 

http://sqlserverquestions.mssqltips.com/questionlist.asp


Saturday, October 13, 2012 - 7:32:58 AM - Nitish Singh Back To Top (19906)

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

Thankyou

 


Friday, October 12, 2012 - 3:41:26 PM - Aaron Bertrand Back To Top (19900)

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").


Friday, October 12, 2012 - 5:52:49 AM - Maheshraj Back To Top (19887)

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.


Thursday, October 11, 2012 - 7:02:02 PM - Aaron Bertrand Back To Top (19885)

Maheshraj, this is a bug I reported a while back:

http://connect.microsoft.com/SQLServer/feedback/details/726826/management-studio-exception-for-localdb-instance

The key is to simply use a RESTORE DATABASE command in a query window instead of trying to do it through the Management Studio wizard.


Thursday, October 11, 2012 - 10:40:56 AM - Maheshraj Back To Top (19879)

Hi Aaron , 

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.

 


Monday, October 1, 2012 - 10:36:03 AM - Jon Back To Top (19753)

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, September 6, 2012 - 10:23:46 AM - Vix Back To Top (19411)

Hi Aaron

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?

Cheers,
Vix

 


Thursday, September 6, 2012 - 9:42:49 AM - Aaron Bertrand Back To Top (19409)

@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?


Thursday, September 6, 2012 - 3:14:44 AM - Vix Back To Top (19405)

Nice stuff!

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.

Any ideas?


Thursday, August 23, 2012 - 9:19:54 AM - Aaron Bertrand Back To Top (19193)

Bijan, you need to find SQLLocalDB.exe on your system and make sure that you call it from its location, by either:

 

(a) using CD in the command window first 

(b) using C:\...path...\SQLLocalDB.exe in your command explicitly

(c) ensuring that the containing folder is part of your PATH environment variable


Thursday, August 23, 2012 - 7:04:04 AM - Bijan Back To Top (19187)

Hi Aaron

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 info  gives 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.

Many thanks

 

 

 


Monday, August 20, 2012 - 2:58:49 PM - Varun Back To Top (19131)

I was struggling to connect to my local database after i installed SQL Server 20120 mngt studio and you post just did what I was expecting.

It saved me from getting mad.. You solved the issue i was trying to resolve after n number of times of reinstallation of mngt studio..

 

Thanks a lot

 

 


Wednesday, May 30, 2012 - 12:02:23 PM - greg aiken Back To Top (17727)

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"...

 

-or-

 

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

 

exec sys.sp_addsrvrolemember @loginame="localappuser", @rolename="sysadmin"

 

go

 

(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')


Wednesday, May 30, 2012 - 8:52:16 AM - Aaron Bertrand Back To Top (17725)
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 - 4:14:38 AM - A.Kadir Bener Back To Top (17717)

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.

Thank you.


Thursday, May 24, 2012 - 3:38:25 PM - Aaron Bertrand Back To Top (17648)
Cheers greg, glad to help out in an unintended way. :-)

Thursday, May 24, 2012 - 1:58:25 PM - greg aiken Back To Top (17647)

you bring up a good point.  

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 - 12:40:22 PM - Aaron Bertrand Back To Top (17646)
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.

Thursday, May 24, 2012 - 12:04:46 PM - Aaron Bertrand Back To Top (17644)
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 - 11:04:17 AM - greg aiken Back To Top (17641)

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...


Wednesday, May 23, 2012 - 6:24:52 PM - Aaron Bertrand Back To Top (17623)
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

Wednesday, May 23, 2012 - 6:23:45 PM - Aaron Bertrand Back To Top (17622)
Hi greg, 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 - 3:49:56 PM - greg aiken Back To Top (17616)

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.

greg


Wednesday, May 16, 2012 - 9:12:35 AM - Kamala Ayyar Back To Top (17481)

Nice article!! Thanks















get free sql tips
agree to terms