Как перезапустить файл pkg sqllocaldb

How do I upgrade SQL Server localDB to a newer version?

Is it possible to upgrade SqlServer localDB from 2012 to 2014?

We currently use version 11 from SQL Server 2012. I need to upgrade to version 12 from SQL Server 2014.

I would like to be able to do it without losing my tables and data.

I installed a new localDB but I then I don’t have my data. It also has another name and I can’t really change the config files since it’s a team project.

I tried using the command line sqlLocalDB tool to create a 2014 version called v11.0 but it created it in the old 2012 version any way.

Why would naming it v11.0 change which version was used?

How can I upgrade the existing v11.0?

6 Answers 6

Update:

Visual Studio 2022 ships with Microsoft SQL Server 2019 15.0.4153.1 LocalDB . If you have Visual Studio 2022 installed but have previously used an earlier version of Visual Studio you can jump to the command sqllocaldb versions below to upgrade.

Original:

This is what I did since Visual Studio 2019 still ships with Microsoft SQL Server 2016 (13.1.4001.0) LocalDB .

I needed to do it because I tried to add Temporal tables with cascading delete that failed.

Failed executing DbCommand (31ms) [Parameters=[], CommandType=’Text’, CommandTimeout=’30’] ALTER TABLE Text SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.Text));

Setting SYSTEM_VERSIONING to ON failed because table ‘Project.Repository.dbo.Text’ has a FOREIGN KEY with cascading DELETE or UPDATE.

Reading up on it it turns out this error only affects SQL Server 2016 and not 2017 and later.

ON DELETE CASCADE and ON UPDATE CASCADE are not permitted on the current table. In other words, when temporal table is referencing table in the foreign key relationship (corresponding to parent_object_id in sys.foreign_keys) CASCADE options are not allowed. To work around this limitation, use application logic or after triggers to maintain consistency on delete in primary key table (corresponding to referenced_object_id in sys.foreign_keys). If primary key table is temporal and referencing table is non-temporal, there’s no such limitation.

This limitation applies to SQL Server 2016 only. CASCADE options are supported in SQL Database and SQL Server 2017 starting from CTP 2.0.

You can get information about your current localDBs by running the command sqllocaldb info in Powershell.

This is quite a good upgrading guide but I choose to do some things a bit differently.

Download SQL Server Express 2019 LocalDB or newer and run the exe.

Select Download Media:

Install from the downloaded exe:

My recommendation is to restart your computer after this but I’m not sure it is needed. I did it anyway.

Checking sqllocaldb versions caused the exception Windows API call "RegGetValueW" returned error code: 0. for me.

Solved it using this answer:

This is how Registry Editor (regedit) looks like in the non working example:

After changing the folder name everything works:

After this back up databases in your current localdb. This will probably not be needed since we will attach all databases from your current localdb to the new version later but if you have sensitive data this is recommended.

I have the standard name MSSQLLocalDB from Visual Studio so my example will use this. As mentioned before you can use sqllocaldb info command to view your current versions.

Run these three commands from powershell:

If everything works the last command should generate something like LocalDB instance "mssqllocaldb" created with version 15.0.2000.5.

Then log into your new LocalDB via SSMS (localdb)\mssqllocaldb or a similar program and attach your old databases. Usually stored in the %UserProfile% folder.

How to reset a SQL Server LocalDB instance in Visual Studio

Many Visual Studio project-templates configure a SQL Server LocalDB instance for development on your local machine. For example the ASP.NET with Identity template.

But what to do if that database gets corrupted or you need a clean one for testing your Entity Framework Migrations, for example?

One solution is this:

1. Open up the Package Manager Console (Tools -> NuGet Package Manager -> Package Manager Console). Make sure to select the project containing your database in the DefaultProject dropdown.
2. Enter the command sqllocaldb info at the prompt. The result is the name of your SQL Server LocalDB instance.
3. Enter the command sqllocaldb stop InstanceName . Replace “InstanceName” with the name you got from the previous command.
4. Enter the command sqllocaldb delete InstanceName . Replace “InstanceName” as in the command before.

Depending on your configuration your database will be re-created automatically when you execute your application or running Update-Database in the Package Manager Console.

2 thoughts on “How to reset a SQL Server LocalDB instance in Visual Studio”

Great… this just destroyed my solution… Local DB was NOT rebuilt after deleting it! How should it?

Hi Alex, sorry for hearing that it did not work out for you. Have you tried running “Update-Database” or something else to have Visual Studio re-create your database? Please comment with a valid email-address next time so you can get any answers on your comment. Just venting your frustration it not very helpful.

Как перезапустить файл pkg sqllocaldb

Программа SqlLocalDB.exe — это простое средство для управления экземплярами LocalDB из командной строки. Оно реализовано как простая оболочка для API экземпляра LocalDB. Как и во многих аналогичных средствах SQL Server (например, SQLCMD), параметры передаются в SqlLocalDB как параметры командной строки, а вывод отправляется на консоль.

Программа SqlLocalDB позволяет разработчикам использовать LocalDB без необходимости писать код для вызова API или использования других средств для этой цели.

Параметры программы SqlLocalDB

SqlLocalDB поддерживает следующие параметры.

Если параметр [version-number] опущен, используется значение по умолчанию — версия сборки SqlLocalDB.

-i запрашивает завершение работы экземпляра LocalDB с параметром NOWAIT.

Программа SqlLocalDB рассматривает пробелы как разделители; имена экземпляров, которые содержат пробелы и специальные символы, необходимо заключать в кавычки. Пример:

chef cookbook не может запустить sqllocaldb с ресурсом выполнения

Я пытаюсь установить ряд ресурсов, включая Azure SDK, на экземпляр Windows Server 2012 R2.

Сначала я получил следующую ошибку в stacktrace:

Я добавил шаги для остановки, удаления, создания и запуска sqllocaldb

Однако теперь я получаю следующую ошибку:

Я подозреваю, что может быть какая-то проблема с синхронизацией (например, sqllocaldb не готов), потому что, если я отправляю ssh на сервер, а затем повторно запускаю chef, вся кулинарная книга обрабатывается идеально и все мои ресурсы устанавливаются.

Примечание. Я пробовал использовать атрибут retries, однако не верю, что execute поддерживает этот атрибут.

Записки программиста

Управление пакетами во FreeBSD при помощи утилиты pkg

Как известно, во FreeBSD можно использовать пакеты как бинарные, так и собранные из исходных кодов при помощи портов. Устройство портов за последнее время ничем не изменилось. А вот на смену утилитам для управления бинарными пакетами pkg_add, pkg_info и прочим pkg_* в последних версиях FreeBSD пришел новый пакетный менеджер pkg (также известный как pkgng). Данная небольшая заметка рассказывает о том, как им пользоваться.

Примечание: Узнать о том, как во FreeBSD раньше происходило управление бинарными пакетами, и о том, как пользоваться портами, вы можете из заметки Установка и обновление софта во FreeBSD. Не исключаю также, что вас могут заинтересовать статьи Использование FreeBSD на десктопе, версия 2.0 и Памятка по обновлению ядра и мира FreeBSD.

Итак, при первом запуске pkg без параметров вы скорее всего увидите такое сообщение:

Отвечаем утвердительно, и ждем, пока pkg установится.

Затем читаем справку:

Посмотреть справку по конкретной команде можно так:

Обновляем информацию о доступных пакетах:

Смотрим список установленных пакетов:

Обновляем установленные пакеты:

Ищем пакет по названию:

Установка пакета/пактетов и всех его/их зависимостей:

Удаляем пакеты, которые больше не нужны:

Смотрим, к какому пакету относится файл:

Посмотреть полный список файлов в пакете можно так:

Загружаем базу известных уязвимостей:

Проверяем установленные пакеты на предмет наличия известных уязвимостей, с ссылками на подробные отчеты:

Проверяем все установленные пакеты на предмет валидности контрольных сумм входящих в пакеты файлов:

Проверяем все установленные пакеты на предмет отсутствия требуемых зависимостей:

Как перезапустить файл pkg sqllocaldb

Is there anything I can do short of reinstalling Visual Studio / Sql Server / Sql Express to fix it?

I should mention that any Visual Studio functionality that is dependent on LocalDb is not working because of this, for example CodeMaps

4 Answers 4

This can occur if you’ve just installed LocalDB and haven’t started the instance yet. I’m not sure if rebooting would have restarted it for me (I imagine it would have), but the following worked without needing to:

That should be all you need — but here is the full diagostics steps I used in the process.

This is before installing new version of LocalDB (2014)

Then I immediately get this error

Strange! What about version 11 — that’s ok

Here’s the versions

Let’s try starting it

Phew! That was easy!

What I ended up doing follows. Note, that I’m sure this is not a correct way of doing this, but rather a messy hack. It allowed me getting my CodeMaps working again though, so I’m happy.

I deleted the mssqllocaldb

The system won’t let me create it again so I created another one:

I went to «C:\Users\[username]\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\» and backed up MSSQLLocalDB folder. Then I copied MSSQLLocalDB1 folder to MSSQLLocalDB .

In registry I found the new instance under HKCU\Software\Microsoft\Microsoft SQL Server\UserInstances\ and changed the DataDirectory value to reflect the new path, that is to end with MSSQLLocalDB instead of MSSQLLocalDB1 .

After that I was able to start MSSQLLocalDB successfully and CodeMaps worked:

PS C:\WINDOWS\system32> sqllocaldb s mssqllocaldb

LocalDB instance «MSSQLLocalDB» started.

How to recreate local.sqlite?

OK. I ran into a recent problem with pkg(8). Where it corrupted it’s sqlite3(1) database of the locally installed ports /var/db/pkg/local.sqlite. I am aware of the option in pkg(8) to (re)create the database from a backup, pkg backup -r /path/to/db/backup .

But given that pkg(8) itself borked the database, and that I need to know I can reliably recreate the database, if/when pkg(8) can’t/won’t. I was wondering if anyone might know the incantation for doing it with sqlite3(1).

I’ve already learned MySQL, and PostgreSQL. But sqlite(1) is quite a bit different.

Thank you for all your time, and consideration.

• 

jb_fvwm2

• Nov 18, 2014

• #2

wblock@

• Nov 18, 2014

• #3

Chris_H

• Nov 18, 2014

• Thread Starter
• #4

wblock@. Yes. I have the backup. I’ve unpacked, and examined it to insure it’s the one I want. But would very much like to how to feed it to sqlite3(1).

jb_fvwm2.
It (pkg(8)) no longer knows most of the previously built/installed ports have been built, and installed. It happened during the course of the build/install of a meta-port, in the ports tree.

30 ports shy of what is currently installed. But it’s a whole lot easier to reconcile, than nearly starting over. Which is what I’ll need to do if I can’t (re)install the backup.

I figure I can simply move the bad copy of local.sqlite aside. Then recreate it via some sqlite3(1) incantation.

I hope my need(s) were clearer this time.

Thank you both, for your replies.

jb_fvwm2

• Nov 18, 2014

• #5

wblock@

• Nov 18, 2014

• #6

Chris_H

• Nov 18, 2014

• Thread Starter
• #7

Thank you, wblock@, for your thoughtful reply.
Indeed it does. I even mentioned that in the OP.
But, as I also mentioned; I was hoping to find out if it’s possible to do it with sqlite3(1). The database engine used to make, and keep the data itself. Point being; if pkg(8) is unable to do the job. How else would I, or anyone else recover?
Thus far, the prospects for anyone recovering from such a scenario look pretty bleak.
Sigh. Looks like I’ll need to go to sqlite3(1) school, to learn yet another SQL language.

Thank you again, wblock@, for taking the time to reply.

wblock@

• Nov 18, 2014

• #8

Chris_H

• Nov 18, 2014

• Thread Starter
• #9

Hello, wblock@, and thanks for the reply.
Indeed. I noticed that too. But was unsure how to move forward. The best I can figure is
cd /var/db/pkg
mv ./local.sqlite ./_bad.local.sqlite
sqlite3 local.sqlite ATTACH PKG.sql
But am unsure. Still reading sqlite3(1), and it’s associated documentation. Hoping to get the correct incantation.

Chris_H

• Nov 18, 2014

• Thread Starter
• #10

OK. Just got brave (or crazy), and gave it a go. Here’s what I did
cd /var/db/pkg
mv ./local.sqlite ./_BAD.local.sqlite
with the backup file in this same directory, as pkg.sql. I then did
sqlite3 local.sqlite
Which gave me the sqlite3(1) prompt
sqlite>
I then issued
sqlite> .read pkg.sql
after some churning. The sqlite3(1) prompt returned. I examined the contents of the directory in another terminal, and discovered there was a new local.sqlite. With a size that I would expect from a DB with as many ports as I had built on this server. So I simply issued
sqlite> .quit
While this all looks promising. I won’t know until I perform some more investigation. But thought it prudent to at least update my progress here. For others that might be following this, and feeling inclined to reply.

Chris_H

• Nov 18, 2014

• Thread Starter
• #11

Thanks for the good times, pkg(8). But I think it’s time I replace you with something I can depend on.

wblock@

• Nov 18, 2014

• #12

Chris_H

• Nov 18, 2014

• Thread Starter
• #13

Equally unsatisfying. Interesting too. Because this backup, and the additional one, also created by periodic(8) both have the same size, and I know they were copies of fully working, and perfectly valid databases. I queried them both, when they were still active. There was absolutely no evidence of problems. Yet neither of them will be accepted by pkg(8), or, perhaps more accurately; sqlite3(1).
I was afraid this day would come. Now there appears to be no salvation.

Thanks for the reply, wblock@. Even if the results were not what I had hoped for.