Sqllocaldb как проверить работу
Кроме полноценного MS SQL Server в версиях Developer или Express мы также можем установить облегченную легковесную версию SQL Server Express — движок LocalDB , который предназначен специально для целей разработки. Например, LocalDB может применяться для разработки программ на различных языках программирования для тестирования их работы с базой данных MS SQL Server, когда нет необходимости в большинстве возможностей стандартного MS SQL Server. И в этих условиях естественно проще установить небольшой легковесный движок, чем полноценный MS SQL Server.
Формально SQLLocalDB представляет компонент MS SQL Server Express, тем не менее есть несколько вариантов, как можно установить LocalDB.
Установка через программу усстановки SQL Server Express
Первый вариант представляет установка LocalDB через программу усстановки SQL Server Express. Так, загрузим с адреса https://www.microsoft.com/en-us/sql-server/sql-server-downloads установщик для SQL Server Express. На стартовом экране установщика выберем пункт «Скачать носитель»:
Нам откроется окно для выбора компонентов для установки. Выберем на нем пункт LocalDB :
После нажатия на кнопку «Скачать» в указанную в окне папку будет загружен файл SqlLocalDB.msi . И после запуска этого файла будет установлен LocalDB.
Установка через мастер установки Visual Studio
Если вы вдруг работаете с Visual Studio, в частности, с платформой ASP.NET, то вы можете установить LocalDB через программу установки Visual Studio. В частности, на вкладке «Отдельные компоненты» можно выбрать соответствующий пункт (конкретная версия LocalDB может отличаться):
Подключение к LocalDB
После установки LocalDB к этой версии SQL Server также можно подключиться из SQL Server Management Studio. Для этого при запуске программы в окне подключения к серверу в поле «Server name» в качестве имени сервера необходимо указать (localdb)\MSSQLLocalDB
Если в этом поле выпадающий список не содержит «(localdb)\MSSQLLocalDB», однако LocalDB тем не менее был ранее установлен, то можно просто ввести в это поле «(localdb)\MSSQLLocalDB».
И после этого мы сможем работать с LocalDB в SQL Server Management Studio в рамках установленых для этого движка ограничений:
How to connect to your local MSSQL server using SSMS?
This post was most recently updated on January 13th, 2023.
Every now and then you run into a situation, where you really need to run some SQL against your local development database. That database, at least in my case, is hosted on your local SQL Server Express.
Connecting to a local SQL Server should be a walk in a park, right? Eh, well…
While using a connection string to connect to said DB is easy, you can’t do that with the SQL Server Management Studio. I wish you could, but hey – it is what it is.
There’s a couple of ways to connect, though! Let’s start with the easy one, that doesn’t always work:
How to connect to your local database with SQL Server Management Studio?
Table of Contents
Connecting to the local instance might work by using the name of the instance. While I’m not sure what’s the reason it hasn’t worked for me (I wonder if the instance names differs based on what you’re installing the SQL Server or SQL Express with. ), maybe it works for you.
Essentially, just paste this into the connection window:
Or like shown below:
How to log in to local SQL Server database using SQL Server Management Studio.
Workaround: Use Named Pipes instead
If it doesn’t work – well, there’s a workaround, although it’s a bit laborious. It in fact requires you to know the instance name pipe – a weird, nonsensical URI-looking piece of textual vomit you simply can’t guess. And then, most of the time, you can just use your local user account to log in.
The workaround has quite a few steps, and finally comes down to this beautiful screen below:
How to log in to local SQL Server using named pipes.
But how do you figure out the right Server name? Check out the steps below!
Time needed: 10 minutes.
-
Try using (localdb)\MSSqlLocalDb first
It won’t hurt testing “(localdb)\MSSqlLocalDb” for Server name – just to see if it works.
The location is something like this under your SQL Server’s installation path -> Tools -> Binn.
Easy enough – run this in your console:
SqlLocalDB.exe
The tool should output something like this:
Next, let’s run the command with parameter “info”.
> SqlLocalDB info
MSSQLLocalDB
ProjectsV13
Now we’ll get to actually run the command for our instance. This’ll look somewhat like below:
SqlLocalDB info [instancename]
You’ll want to get this server up and running. That’s easy – just run the command below:
SqlLocalDB start MSSQLLocalDB
The output might be something like below:
Or in text form:
SqlLocalDB info MSSQLLocalDB
Name: MSSQLLocalDB
Version: 13.1.4001.0
Shared name:
Owner: [username]
Auto-create: Yes
State: Running
Last start time: 2020-08-12 1:22:49 PM
Instance pipe name: np:.\pipe\LOCALDB#A4E758FA\tsql\query
Pay very close attention to the state – it should now be “running” – and note “Instance pipe name“!
And boom! You should be good.
Frequently asked questions
The executable installation file is hosted by Microsoft – they keep changing the shortlink of the actual download, so your best bet is to download it from the Learn/Docs page describing the tool – here: https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16
References and appendices
Updated 13.2.2020: Added mention of the easier method, thanks Mika Berglund.
For further reference, check out these links:
Appendix 1: the output of SqlLocalDB.exe
I’m including the whole default output of SqlLocalDB.exe here, as it won’t fit into the step-by-step instructions above.
Antti Koskela is a proud digital native nomadic millennial full stack developer (is that enough funny buzzwords? That’s definitely enough funny buzzwords!), who works as Solutions Architect for Precio Fishbone, building delightful Digital Workplaces.
He’s been a developer from 2004 (starting with PHP and Java), and he’s been working on .NET projects, Azure, Office 365, SharePoint and a lot of other stuff. He’s also Microsoft MVP for Office Development.
This is his personal professional (e.g. professional, but definitely personal) blog.
-
— February 14, 2023 — February 7, 2023 — January 31, 2023
Posts Related to «How to connect to your local MSSQL server using SSMS?»:
report this ad
Search this site!
About the site and the author
Welcome! You just stumbled upon the home page of an all-around artisan code crafter and Microsoft MVP, Antti «koskila» Koskela.
Don’t hesitate to leave comments. I read them all and try to reply as well!
More information about me in the About -section!
Solutions are worthless unless shared!
Check out the tech & programming tips, often about ASP.NET MVC, Entity Framework, Microsoft SharePoint Server & Online, Azure, Active Directory, Office 365 or other parts of the ever-growing and more and more intimidating stack that Microsoft offers us.
I’ve been developing both classic server stuff, but also (and actually especially) more cloud-oriented stuff in the past 15 years.
There’s an occasional post about software issues other than on Microsoft’s stack, and a rare post about hardware, too! And sometimes I might post about my sessions at different community events, or experiences as an expat living in a foreign country (in 2017, that country was the USA, in 2018 & 2019 Canada).
And since I’m hosting this site on WordPress, and boy does WordPress experience a lot of issues, I might also post something about solving those cases. Like PHP compatibility issues.
report this ad
Want the latest tips directly to your inbox?
Like these posts and tips? You can get them automatically right as I post them! Enter your email here or check out the RSS feed here: https://www.koskila.net/feed/
And no worries — it’s just notifications of new posts coming in, nothing else 🙂
Установка и настройка Microsoft SQL Server Express LocalDB (x86/x64)
Для работы с программами ДНД Софт Вы можете использовать Microsoft SQL Server Express LocalDB . LocalDB — это очень обрезанная версия SQL Server, но функций этой версии полностью хватает для работы в нашей программе (ДНД ЭТЛ Профессионал .Нет и др.) на локальном ПК. Преимуществ у LocalDB два — это быстрая установка и маленький размер программы, что позволит сильно сэкономить место на жестком диске.
Теперь поговорим об установке LocalDB на Вашу операционную систему.
Если у Вас 32-х битная (x86) операционная система Windows 7 — 10, то обязательно устанавливайте версию SQL LocalDB 2014 x86. SQL LocalDB версии 2016 и выше уже не поддерживают 32-битные операционные системы.
Если у Вас Windows 7 x64, то:
Если у Вас Windows 8-10, то:
В некоторых случаях при установке дистрибутива SQL LocalDB 2019 установщик останавливается на запуске служб и дальше установка не идет. В этом случае рекомендуем устанавливать 2017 или 2016 дистрибутивы SQL LocalDB:
Посмотреть какой разрядности Ваш компьютер (x86 или x64) можно в сведениях компьютера, см. Рисунок 1.
Рисунок 1 — Сведения о Вашем компьютере.
После установки СУБД SQL Server LocalDB, вы можете использовать его в наших программах для создания базы данных. Для этого в реквизите «Выберите экземпляр SQL Server» введите «(localdb)\MSSQLLocalDB» и выберите Windows Аутентификацию. MSSQLLocalDB — это экземпляр который создается по умолчанию при установке LocalDB. См. рисунок 2.
Рисунок 2 — Окно «Соединение с сервером базы данных»
Если у Вас возникли проблемы с работой SQL Server LocalDB, то запустите командную строку от имени администратора (как запустить командную строку от имени администратора →). В строке введите следующую команду:
Должен появится список всех экземпляров, в этом списке должен быть экземпляр MSSQLLocalDB. Если его нет или выводятся ошибки, то пересоздайте экземпляр командами:
SqlLocalDB.exe stop MSSQLLocalDB
SqlLocalDB.exe delete MSSQLLocalDB
SqlLocalDB.exe create MSSQLLocalDB
SqlLocalDB.exe start MSSQLLocalDB
После этой операции попробуйте снова команду:
Проверьте присутствует ли экземпляр MSSQLLocalDB и если он там есть, то пробуйте использовать его для создания БД в наших программах.
How to connect and use Microsoft SQL Server Express LocalDB
Microsoft SQL Server Express LocalDB is intended for developers, it is very easy to install and doesn’t require any complex configuration task to create an instance or to use the database. The Introduction on the installation of Local DB be found on “How to install Microsoft SQL Server Express LocalDB” article.
This article will show different ways of connecting to LocalDB and how to create and use an instance of LocalDB.
LocalDB runs on demand, which means that the LocalDB processes can start and be stopped automatically when needed. In practice, this will happen when a computer is started and there is no LocalDB process running. So, there are no resources dedicated to servicing a database server during one’s daily work routine. When an application that uses Microsoft SQL Server Express LocalDB is run and a connection is made to the database, the LocalDB process is started on the computer aka on-demand. When the last connection from the application is closed, after a short delay, then the LocalDB process ends.
Also, the attached database file name property can be used for the database connection string. In this way, the location of the database file can be controlled. The database file doesn’t have to be searched in some centralized SQL Server location, but it can be found in user’s Application Install folder. This means that the work is only being done with a database file, not with configuring and using a database server.
Once Microsoft SQL Server Express LocalDB is installed, using SqlLocalDB Utility can be seen as information about the LocalDB instances installed on the machine. By typing the SqlLocalDb info command and pressing the Enter key from the keyboard in the Command Prompt window, the following instances may be found:
The v11.0 and MSSQLLocalDB instances are automatic Microsoft SQL Server Express LocalDB instances and come with installation of Microsoft SQL Server Express LocalDB.
Depending on which version of Microsoft SQL Server Express LocalDB is installed on the machine, the different automatic LocalDB instance will appear. For example, if only Microsoft SQL Server 2012 Express LocalDB is installed, when the available LocalDB instances on a machine using the SqlLocalDb info command are listed, the v11.0 LocalDB instance will appear. But, if Microsoft SQL Server 2014 Express LocalDB or a newer one is installed, the MSSQLLocalDB instance will appear as the automatic instance.
Naming conventions of the automatic v11. 0 LocalDB instance can be taken from this link and naming conventions for the automatic MSSQLLocalDB LocalDB instance can be found on this link.
To see the other details about instances, for example v11.0 instance, type the following command:
SQLLocalDB info v11.0
The result will be as follows:
Name stands for the name of the LocalDB instance,
Version shows the full version of the LocalDB instance,
Shared name shares the instance using the specified shared name, when multiple users need to connect to a single instance of Microsoft SQL Server Express LocalDB.
Type the following command: SQLLocalDB share v11.0 Mare in the Command Prompt window and execute. In the Command Prompt window will show the following message:
Private LocalDB instance “v11.0” shared with the shared name: “Mare”.
Make sure that the Command Prompt window is running under the administrator privileges, otherwise the following message may occur:
Sharing of LocalDB instance “v11.0” with shared name “Mare” failed because
Administrator privileges are required in order to execute this operation.
Now, when the SQLLocalDB info v11.0 command is executed, the result will be this one:
Owner shows the name of the Windows User (the owner of the LocalDB instance)
Auto-create indicates (shows) if LocalDB instance automatic is created or not. If the LocalDB instance is automatically created in the Auto-create section, Yes will appear. On the other hand, No will be shown.
Let’s create a new instance and demonstrate this. In the Command Prompt window, type the following command: SqlLocalDB create NewInstance and press the Enter key. The message will appear which indicates that the LocalDB instance is created:
LocalDB instance “NewInstance” created with version 13.0.2186.6.
Now, when the SqlLocalDB info NewInstance command is executed in the Auto-create section, No will be shown:
As it may be noticed, the version of the NewInstance LocalDB is different from the version of the v11.0 LocaDB instance. This happens, when on a machine, more than one version of Microsoft SQL Server Express LocalDB have been installed.
Now, when a new instance of LocalDB is created without specifying a version of Microsoft SQL Server Express LocalDB after a name of the LocalDB instance in the creating strings, by default, the higher version of the LocalDB will be used in creating the process of a new LocalDB instance.
Let’s create a new instance which will have the same version as the v11.0 LocalDB instance. Type the following command: SqlLocalDB create Test 11.0 in the Command Prompt window and press the Enter key. The following message will appear:
LocalDB instance “Test” created with version 11.0.2318.0.
Now, when the SqlLocalDB info Test command is executed, the result will be this one:
When creating a new LocalDB instance with the name which have two or more words speared by a space, for example Test instance, without putting them in the double quotation mark, the following message will appear:
Creation of LocalDB instance “Test” with version instance failed because of the following error
The parameter for the LocalDB Instance API method is incorrect. Consult the API documentation.
If executed the SQLLocalDB create “Test instance” command, the following message will appear in the Command Prompt window:
LocalDB instance “Test instance” created with version 11.0.
State shows a current state of a chosen LocalDB instance, if it is Running or it Stopped. To run, for example, the Test LocalDB instance, in the Command Prompt window, the following command should be typed: SqlLocalDB start Test. The result after starting the Test LocalDB instance is:
Now, the State is changed to “Running” and the Instance pipe name section has a value. This value (string) is used as the connection string to the Test LocalDB instance from another application.
Now, when an instance is created and started, it is not possible to do other things like creating databases and running queries since SqlLocalDb does not provide an interface to the engine by itself as it does not provide a means to interact with databases. Still, there are several other options to connect to and interact with SqlLocalDb instances.
For example, if the string from the Instance pipe name is pasted in the Server name field under the Connect to Server window of SQL Server Management Studio:
And the Connect button is pressed, the connection to the Test LocalDB instance will be established:
Now, when the connection is established, the same T-SQL code can be used in LocalDB as it is in SQL Server Express when creating databases, tables, stored procedures etc.
There are some restrictions related to LocalDB comparing to SQL Server Express, which can be seen here.
Another way to connect to the LocalDB instance is to type the (LocalDB)\ followed by the name of the LocalDB instance (v11.0) in the Server name combo box of the Connect to Server window and to click the Connect button:
In the Object Explorer window, the result will be like this:
If in the Server name combo box of the Connect to Server window is entered (LocalDB)\ followed by a dot ((LocalDB)\.):
The connection will be established with the automatic (default) LocalDB instance:
This type of connection to the automatic LocalDB instance refers to Microsoft SQL Server Express 2014 LocalDB or newer.
As it is mentioned in the text above, if more than one LocalDB versions are installed on the machine, the connection will be established with the automatic LocalDB instance with higher (latest) version.
Let’s delete that automatic LocalDB instance. In this situation, the MSSQLLocalDB instance is the one that should be deleted. In the Command Prompt window, the following command should be typed: SqlLocalDB delete MSSQLLocalDB. When the Enter key is pressed from the keyboard, the following message may occur:
Delete of LocalDB instance “MSSQLLocalDB” 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.
As the message says, the automatic MSSQLLocalDB instance is in use. In order to be deleted, first it should be stopped. Type the SqlLocalDB stop MSSQLLocaDB command in the Command Prompt window:
LocalDB instance “MSSQLLocalDB” stopped.
Now, repeat the SqlLocalDB delete MSSQLLocalDB command. This will successfully remove LocalDB instance from the machine:
LocalDB instance “MSSQLLocalDB” deleted.
Using sqlcmd, the connection with the LocalDB instance, can be established. From there, T-SQL command and various T-SQL queries can be used. In the Command Prompt window, navigate to the SqlLocalDB.exe: cd C:\Program Files\Microsoft SQL Server\110\Tools\Binn\
Also, type the following command: sqlcmd-S (localDB)\v11.0 in order to connect to v11.0 LocaDB instance.
When there is the connection to the v11.0 LocalDB instance, a database, a table, etc. can be created. Past the following query in the Command Prompt window: