sql本地数据库实例不存在 [英] sql local db instance doesn't exist

问题描述

1. 我成功使用本地系统帐户创建了一个实例. 然后我用"info"命令检查了实例信息，显示了实例的所有信息. 但是几秒钟后，我再次运行"info"命令，实例被删除. 为什么删除实例? 本地数据库实例已删除
2. 我有一个在本地系统帐户下运行的窗口服务. 该服务可以使用"MSSQLLocalDB"实例连接到SQL Local DB，但是尽管共享了私有实例，却无法使用私有实例连接. 大家可以给我提个解决这个问题的建议吗?

解决方案

一般观察

由本地系统创建的

LocalDB实例有问题.请从

问题2的答案

我发现，通过作为本地系统运行的进程连接到本地系统拥有的LocalDB实例的唯一一致方法是指定命名管道.连接字符串中实例的地址.即

Server=np:\\.\pipe\LOCALDB#xxxxxxxx\tsql\query

代替

Server=(localdb)\.\{instance name}

您可以使用以下语法，使用sqllocaldb.exe工具查找LocalDB实例的命名管道地址:

sqllocaldb info {instance name}

您可能在不同的操作系统(32位和64位)或不同版本的SQL LocalDB或它们的组合上获得了成功，但是我发现只有在使用命名管道地址进行连接时，才能获得一致的成功.如问题2的答案所述，尝试通过实例名称进行连接可能会导致连接失败，甚至更糟.

1. I created an instance with Local System Account successfully. Then I checked the instance information with "info" command, all information of instance was shown. But after a few seconds I run "info" command again, the instance was deleted. Why was the instance deleted? localdb instance was deleted
2. I have a window service run under Local System Account. This service can connect to SQL Local DB by using "MSSQLLocalDB" instance , but could not connect by using a private instance although the private instance was shared. Could everyone give me an advice to solve this problem, please?

解决方案

General Observation

LocalDB instances created by Local System are problematic. Refer the following from the MS doc for SQL LocalDB:

I have encountered both of the problems you mention. I have found workarounds for these and many other related issues, but it has been a painful process. If I had to start over again, I would heed the Microsoft advice and avoid instances owned by Local System, or use something other than SQL LocalDB.

Caveats:

• The information below applies only to instances owned by Local System. The file paths, registry entries, and caveats below are different for instances owned by normal Windows accounts.
• Some paths may be different when running in 32-bit Windows--I only use 64-bit Windows.

Answer to question 1

Most likely, your instance has been corrupted, not deleted. Try to create the instance again. If creation is unsuccessful, the existing corrupt instance is preventing re-creation, deletion, and connection. Such corruption can occur to instances created by the Local System account in a 64-bit process, but subsequently accessed in a 32-bit process also running as Local System (or vice-versa).

The only way I've found to completely remove the corrupted instance is to

1. Delete the folder for the corrupt instance from either here (if created in a 64-bit process):

   • C:\Windows\System32\config\systemprofile\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\{instance name}

     or here (if created in a 32-bit process):

   • C:\Windows\SysWOW64\config\systemprofile\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\{instance name}

2. And remove the associated registry entries.

   • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server Local DB\Shared Instances\{instance name} - (this exists only if you shared the instance)
   • HKEY_USERS\S-1-5-18\Software\Microsoft\Microsoft SQL Server\UserInstances\{GUID} - (choose the GUID which has its DataDirectory path value ending in the instance name)

After all this, when creating the instance again, ensure that you are both creating and accessing the instance from processes having the same "bitness" (32 or 64.) This is tedious and difficult to enforce, unless you own the machine and control all access to it.

Answer to question 2

I have found that the only consistent way to connect to a LocalDB instance owned by Local System via a process running as Local System, is to specify the named-pipe address of the instance in the connection string. I.e.,

Server=np:\\.\pipe\LOCALDB#xxxxxxxx\tsql\query

in place of

Server=(localdb)\.\{instance name}

You can find the named-pipe address of a LocalDB instance with the sqllocaldb.exe tool, using the following syntax:

sqllocaldb info {instance name}

You may have success on different operating systems (32- vs. 64-bit) or different versions of SQL LocalDB, or combination thereof, but I have found consistent success only when using the named-pipe address to connect. Attempting to connect via the instance name can either fail to connect, or even worse, corrupt the instance, as mentioned in the answer to question #2.

这篇关于sql本地数据库实例不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！