Lost SQL Source Control Links using localdb

skuhn

Me and my team are using SSMS 17.7 along with SQL Source Control 6.0.2.6908 with git 2.17 on Windows. We have recently shifted from using a shared development database to using dedicated databases with localdb.  We have observed that from time to time, the SQL Source Control links to our localdb databases are lost.  We have 8 databases so it's time consuming to re-establish the links. This doesn't ever happen with our server databases.  Any troubleshooting advice would be helpful for us to identify the cause of the issue.

philwjones

Just happened to me.

I discovered my source control links had disappeared too - I think it must've happened when I installed 6.1.8.7584.  Fortunately, I was able to restore a full version of LinkedDatabases.xml in the appdata folder (using previous versions) which saved a lot of pain relinking.

LeeM

Thanks philwjones, solved my same problem very easily.  I was just able to find the LinkedDatabases.xml from a previous rev folder under AppData/Local/Redgate.  Pulled the file from the Version 6 dir to the Version 7 dir and all my links are back.

Louis_Gigantisch

I have the same problem, but there is no Version 6/7 change here. I've always been on 7, and Source Control forgets my links every time (or maybe just when there is a software update, which is almost every time I use the software). The LinkedDatabases.xml file is right were it's supposed to be, and contains the links I made.
Any ideas?

Louis_Gigantisch

For future reference, this issue seems relevant: https://forum.red-gate.com/discussion/78940/localdb-db-linking-lost-when-management-studio-crashes

Louis_Gigantisch

Yes, that issue was indeed relevant. LocalDB changes its server instance name every time it is restarted (e.g. when your machine reboots). You can get the new name by connecting to it in SQL Management Studio and executing this query: SELECT SERVERPROPERTY('InstanceName')
Insert the resulting instance name in the LinkedDatabases.xml file.

I can't believe Redgate hasn't handled this issue after 2 years.

Louis_Gigantisch

So, updating the LinkedDatabases.xml file isn't enough. There are at least 3 other xml files that need to be updated.
I started on a Python script to update the files automatically, but even after that, there's still something missing, as many of my tables appear to be unlinked after doing so.
I'll put my w.i.p. script here for some unfortunate future soul that tries to attempt the same, but I think it's pretty clear RedGate Source control does not support LocalDB! They should put that on the box…

Anyway, so here is the script:

import os, re
os.popen('sqllocaldb s MSSQLLocalDB')
stream = os.popen('sqllocaldb i MSSQLLocalDB')
output = stream.read()
match = re.search(r'pipe\\(.+?)\\', output)
instanceName = match[1]

for fn in (
        r'C:\Users\[YOUR USERNAME HERE]\AppData\Local\Red Gate\SQL Source Control 7\LinkedDatabases.xml',
        r'C:\Users\[YOUR USERNAME HERE]\AppData\Local\Red Gate\SQL Source Control 7\DifferenceFilters.xml',
        r'C:\Users\[YOUR USERNAME HERE]\AppData\Local\Red Gate\SQL Source Control 7\OptionsStore.xml',
        r'C:\Users\[YOUR USERNAME HERE]\AppData\Local\Red Gate\SQL Source Control 7\TableDataConfigs.xml'
    ):
    with open(fn, encoding='utf16') as f:
        a = f.read()
    b = re.sub(r'(.+?\\).+?',
        r'\1'+instanceName+'', a)
    with open(fn, 'w', encoding='utf16') as f:
        f.write(b)
        #f.write(a)