Denali – Day 18: Engine: Express LocalDB–Command line

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

About these ads
This entry was posted in Denali, Installation, Whats New and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s