Lost migration script in SSMS

AdamY

I recently created a migration script to cover some changes to a table and sprocs. When I committed the changes yesterday, the file disappeared from the "Migrations" tab of SQL Source Control. I have version 3.4.1.73 of SQL Source Control and I did check in these changes with the #ignorepolicies tag. I have restarted SSMS and rebooted my machine, but the issue persists. The only file that shows is one I committed 2 months ago.


It seems the only part broken is the SQL Source Control because...
[1] The script does show in TFS and is in my local TFS file directory.



[2] When using SQL Compare to create a change script, it does recognize the migration script and use it.

The Migration Scripts location is correct in SQL Source Control.


How do I get SQL Source Control to show the file again? Thank you for your help.

Chris Auckland

Thanks for your post, and sorry you're having trouble with the migration scripts.

In order for SQL Source Control to consider the migration script, the database revisions need to be correct in the database level extended properties, and the database needs to be linked to the exact same repository. We don't currently support cross branch migration scripts, so if something doesn't match up, then we err on the side of caution and ignore it.

Can you take a look at 'CompassionWeb 11234 to 11466.migrationScript' and also at the linked database's extended properties and see if the repository locations all match up?

AdamY

The database revision changes every time changes are checked in so I don't understand how that would cause a problem. The current DB revision (in extended properties) is 11468, but I have checked in changes since the migration script check-in so it should be different. And the older migration script is still showing (10507 to 10508). I'm not doing cross-branch migration scripts. SQL Compare is seeing the "missing" migration script, too.

I checked the repository locations and found this...

The DB extended property "SQLSourceControl Scripts Location" shows:

<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<ISOCCompareLocation version="1" type="TfsLocation">
<ServerUrl>https://tfs.ci.org:8143/tfs/</ServerUrl&gt;
<SourceControlFolder>$/Compassion.com/Source - Database/Dev/CompassionWeb</SourceControlFolder>
</ISOCCompareLocation>

The DB extended property "SQLSourceControl Migration Scripts Location" shows:

<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<ISOCCompareLocation version="1" type="TfsLocation">
<ServerUrl>https://tfs.ci.org:8143/tfs/</ServerUrl&gt;
<SourceControlFolder>$/Compassion.com/Source - Database/Dev/Migration Scripts</SourceControlFolder>
</ISOCCompareLocation>

'CompassionWeb 11234 to 11466.migrationScript' shows:

<MigrationScript version="2" type="MigrationScript">
<Name>CompassionWeb 11234 to 11466</Name>
<FromVersion version="1" type="VersionInfoRaw">
<Version>11234</Version>
<ScriptFolderLocation><?xml version="1.0" encoding="utf-16" standalone="yes"?>
<!---->
<ISOCCompareLocation version="1" type="TfsLocation">
<ServerUrl>https://tfs.ci.org:8143/tfs/</ServerUrl&gt;
<SourceControlFolder>$/Compassion.com/Source - Database/Dev/CompassionWeb</SourceControlFolder>
</ISOCCompareLocation></ScriptFolderLocation>
</FromVersion>
<ToVersion version="1" type="VersionInfoRaw">
<Version>11466</Version>
<ScriptFolderLocation><?xml version="1.0" encoding="utf-16" standalone="yes"?>
<!---->
<ISOCCompareLocation version="1" type="TfsLocation">
<ServerUrl>https://tfs.ci.org:8143/tfs/</ServerUrl&gt;
<SourceControlFolder>$/Compassion.com/Source - Database/Dev/CompassionWeb</SourceControlFolder>
</ISOCCompareLocation></ScriptFolderLocation>
</ToVersion>

And the migration file that is showing up properly also has the same values. "CompassionWeb 10507 to 10508.migrationScript" shows:

<MigrationScript version="2" type="MigrationScript">
<Name>CompassionWeb 10507 to 10508</Name>
<FromVersion version="1" type="VersionInfoRaw">
<Version>10507</Version>
<ScriptFolderLocation><?xml version="1.0" encoding="utf-16" standalone="yes"?>
<!---->
<ISOCCompareLocation version="1" type="TfsLocation">
<ServerUrl>https://tfs.ci.org:8143/tfs/</ServerUrl&gt;
<SourceControlFolder>$/Compassion.com/Source - Database/Dev/CompassionWeb</SourceControlFolder>
</ISOCCompareLocation></ScriptFolderLocation>
</FromVersion>
<ToVersion version="1" type="VersionInfoRaw">
<Version>10508</Version>
<ScriptFolderLocation><?xml version="1.0" encoding="utf-16" standalone="yes"?>
<!---->
<ISOCCompareLocation version="1" type="TfsLocation">
<ServerUrl>https://tfs.ci.org:8143/tfs/</ServerUrl&gt;
<SourceControlFolder>$/Compassion.com/Source - Database/Dev/CompassionWeb</SourceControlFolder>
</ISOCCompareLocation></ScriptFolderLocation>

Does any of that help you? I'm at a loss right now. I'm considering undoing all the DB changes, then changing them back and trying it over again - but I'm a little concerned that will cause other problems.

AdamY

I unlinked the database from TFS, then relinked it (using the same location for Migration Scripts and the db code as I did before). Everything seems okay now. Looks like it just needed a "reboot".

IT Crowd says it best: http://youtu.be/p85xwZ_OLX0

Chris Auckland

Thanks for your reply. The good old turn it off and on again trick to the rescue.

I hope it behaves from now on.