Denali – Day 18: Engine: Express LocalDB–Command line
Express LocalDB is an express edition with minimum files required to start sql server for developers to work. This is only for developers assuming they donot have much knowledge of DBA and do not want to know much, they are more focus on their localDB to work on with.
It is a kind of Express edition, of very small in size less then 35MB(27 MB for x86 and 33MB for x64) compared to regular express edition >100MB.
Installation: Very easy to install, just download Sql Server LocalDB it for free and Next, Next, Install …. Finish. Screen and you are done.
The only major requirement is OS should be windows 7 or above and.NET 4.0.2.
As stated, this is only for developers to work with db, so by default no management studio or any connectivity tool installed on it. It works with a command prompt utility called SqlLocalDB,SqllocalDB.exe.
SqlLocalDB.exe
{
[ create | c ] <instance-name> <instance-version> [-s ]
| [ delete | d ] <instance-name>
| [ start | s ] <instance-name>
| [ stop | p ] <instance-name> [ -i ] [ -k ]
| [ share | h ] [" <user_SID> " | " <user_account> " ] ” <private-name> ” ” <shared-name> “
| [ unshare | u ] ” <shared-name> “
| [ info | i ] <instance-name>
| [ versions | v ]
| [ trace | t ] [ on | off ]
| [ help | -? ]
}
When you install, the default instance is installed and named as “v11.0″. All the system databases are located at “AddData” folder for user profile which is “Hidden” as developer is less bother about and it and they do not want to know much on it.
we could create an named instance. Using command
SqlLocalDB.exe create “InstanceNm”
SqlLocalDB.exe start “InstanceNm”
And once instance created you can start/stop/ or even delete if required.
Default instance are “public” and named instance are “private”.
All instance are unshared, you can make the instance as shared or unshared using option of SqlLocalDB.exe or using API
LocalDBShareInstance and LocalDBUnShareInstance.
To get information about the instance use –info
SqlLocalDB.exe –info “InstanceNm”
Instance pipe name | np:\\.\pipe\LOCALDB#xxxxxxxx\tsql\query | |
To use the instance in scripting – You have to access the instance using the “instance pipe name” which has LocalDB#xxxxxxxx hex number and will change every time instance starts.
Management Stuio:
We can explicitly install the Express Management studio for the GUI to work with and register the instance “.\InstanceName” or (localdb)\v11.0
Sqlcmd:
Sqlcmd is another cmd line utility using which we can work on instances which also require sql express client tool.
Sql Server Data Tools(SSDT):
These are the best tool a DBA can work with. But as this is for developers developer can try the new SSDT(Sql Server Data Tools) which is a visual studio 2010 tool using which you can easily connect to LocalDB, by just created a Sql server new project, or add existing server.
By default user database is created on user profile % %USERPROFILE%
\ user-name folder. Which can be changed at the time of creating a DB.
Create database abc on(name=’abc’, filename =’d:\data\abc.mdf’)
Ref:
http://msdn.microsoft.com/en-us/library/hh510202.aspx
http://blogs.msdn.com/b/sqlexpress/archive/2011/10/28/localdb-where-is-my-database.aspx
http://www.mssqltips.com/tipprint.asp?tip=2694
