On of my project we switched from TDS to Unicorn. After we configured Unicorn and set up dependencies between configurations I wanted to verify that every item was migrated from TDS (*.item) to Unicorn (*.yml) in the database successfully.
I wanted to check by the following points:
- Is there any difference between field values?
- Detect missing items
- Detect missing field values
First I needed to setup one Sitecore instance for TDS (tds910) and one for Unicorn (unicorn910). I deployed (TDS) and syncronized (Unicorn) the items and then ran the following few scripts.
In most cases I needed to compare 2 tables – SharedFields or VersionedFields. In your case maybe you need to also run the scripts above for UnversionedFields.
Detect difference between field values
I ignored the following fields to give me meaningful results:
- Updated
- Revision
- Created
- Updated by
- Owner
- Created by
- Lock
- Blob
You can add more filters or remove these from the script below.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| PRINT 'Different values on the same items - Shared Fields' | |
| select tds.[ItemId], | |
| tds.[FieldId], | |
| tds.[Value] as 'TDS Value', | |
| unicorn.[Value] as 'Unicorn Value' | |
| from [tds910_Master].[dbo].[SharedFields] as tds | |
| join [unicorn910_Master].[dbo].[SharedFields] unicorn | |
| on tds.ItemId = unicorn.ItemId | |
| and tds.FieldId = unicorn.FieldId | |
| and tds.FieldId != 'D9CF14B1-FA16-4BA6-9288-E8A174D4D522' /* __Updated */ | |
| and tds.FieldId != '8CDC337E-A112-42FB-BBB4-4143751E123F' /* __Revision */ | |
| and tds.FieldId != '25BED78C-4957-4165-998A-CA1B52F67497' /* __Created */ | |
| and tds.FieldId != 'BADD9CF9-53E0-4D0C-BCC0-2D784C282F6A' /* __Updated by */ | |
| and tds.FieldId != '52807595-0F8F-4B20-8D2A-CB71D28C6103' /* __Owner */ | |
| and tds.FieldId != '5DD74568-4D4B-44C1-B513-0AF5F4CDA34F' /* __Created by */ | |
| and tds.FieldId != '001DD393-96C5-490B-924A-B0F25CD9EFD8' /* __Lock */ | |
| and tds.FieldId != '40E50ED9-BA07-4702-992E-A912738D32DC' /* Blob */ | |
| and tds.Value != unicorn.Value | |
| PRINT 'Different values on the same items - Versioned Fields' | |
| select tds.[ItemId], | |
| tds.[FieldId], | |
| tds.[Language], | |
| tds.[Value] as 'TDS Value', | |
| unicorn.[Value] as 'Unicorn Value' | |
| from [tds910_Master].[dbo].[VersionedFields] as tds | |
| join [unicorn910_Master].[dbo].[VersionedFields] unicorn | |
| on tds.ItemId = unicorn.ItemId | |
| and tds.Language = unicorn.Language | |
| and tds.FieldId = unicorn.FieldId | |
| and unicorn.Value != 'sitecore\unicorn' | |
| and tds.FieldId != 'D9CF14B1-FA16-4BA6-9288-E8A174D4D522' /* __Updated */ | |
| and tds.FieldId != '8CDC337E-A112-42FB-BBB4-4143751E123F' /* __Revision */ | |
| and tds.FieldId != '25BED78C-4957-4165-998A-CA1B52F67497' /* __Created */ | |
| and tds.FieldId != 'BADD9CF9-53E0-4D0C-BCC0-2D784C282F6A' /* __Updated by */ | |
| and tds.FieldId != '52807595-0F8F-4B20-8D2A-CB71D28C6103' /* __Owner */ | |
| and tds.FieldId != '5DD74568-4D4B-44C1-B513-0AF5F4CDA34F' /* __Created by */ | |
| and tds.FieldId != '001DD393-96C5-490B-924A-B0F25CD9EFD8' /* __Lock */ | |
| and tds.FieldId != '40E50ED9-BA07-4702-992E-A912738D32DC' /* Blob */ | |
| and tds.Value != unicorn.Value |
Detect missing items
This is quite obvious: I would like to have the list of items which are in TDS but not in Unicorn accidentally.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| PRINT 'Items which are in TDS but not in Unicorn' | |
| select tds.ID as 'TDS ItemId', | |
| unicorn.ID as 'Unicorn ItemId' | |
| from [tds910_Master].[dbo].[Items] as tds | |
| left join [unicorn910_Master].[dbo].[Items] unicorn | |
| on tds.ID = unicorn.ID | |
| where unicorn.ID is null |
Detect missing field values
This one also quite obvious: I would like to have the list of field which are in TDS but not in Unicorn accidentally.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| PRINT 'Fields values which are in TDS but not in Unicorn - Shared Fields' | |
| select tds.[ItemId], | |
| unicorn.[ItemId], | |
| tds.[FieldId], | |
| tds.[Value] as 'TDS Value', | |
| unicorn.[Value] as 'Unicorn Value' | |
| from [tds910_Master].[dbo].[SharedFields] as tds | |
| left join [unicorn910_Master].[dbo].[SharedFields] unicorn | |
| on tds.ItemId = unicorn.ItemId | |
| and tds.FieldId = unicorn.FieldId | |
| where unicorn.FieldId is null | |
| and tds.FieldId != 'D9CF14B1-FA16-4BA6-9288-E8A174D4D522' /* __Updated */ | |
| and tds.FieldId != '8CDC337E-A112-42FB-BBB4-4143751E123F' /* __Revision */ | |
| and tds.FieldId != '25BED78C-4957-4165-998A-CA1B52F67497' /* __Created */ | |
| and tds.FieldId != 'BADD9CF9-53E0-4D0C-BCC0-2D784C282F6A' /* __Updated by */ | |
| and tds.FieldId != '52807595-0F8F-4B20-8D2A-CB71D28C6103' /* __Owner */ | |
| and tds.FieldId != '5DD74568-4D4B-44C1-B513-0AF5F4CDA34F' /* __Created by */ | |
| and tds.FieldId != '001DD393-96C5-490B-924A-B0F25CD9EFD8' /* __Lock */ | |
| and tds.FieldId != '40E50ED9-BA07-4702-992E-A912738D32DC' /* Blob */ | |
| PRINT 'Field values which are in TDS but not in Unicorn - Versioned Fields' | |
| select tds.[ItemId], | |
| unicorn.[ItemId], | |
| tds.[FieldId], | |
| tds.[Value] as 'TDS Value', | |
| unicorn.[Value] as 'Unicorn Value' | |
| from [tds910_Master].[dbo].[VersionedFields] as tds | |
| left join [unicorn910_Master].[dbo].[VersionedFields] unicorn | |
| on tds.ItemId = unicorn.ItemId | |
| and tds.FieldId = unicorn.FieldId | |
| and tds.Language = unicorn.Language | |
| where unicorn.FieldId is null | |
| and tds.Value != '' | |
| and tds.FieldId != 'D9CF14B1-FA16-4BA6-9288-E8A174D4D522' /* __Updated */ | |
| and tds.FieldId != '8CDC337E-A112-42FB-BBB4-4143751E123F' /* __Revision */ | |
| and tds.FieldId != '25BED78C-4957-4165-998A-CA1B52F67497' /* __Created */ | |
| and tds.FieldId != 'BADD9CF9-53E0-4D0C-BCC0-2D784C282F6A' /* __Updated by */ | |
| and tds.FieldId != '52807595-0F8F-4B20-8D2A-CB71D28C6103' /* __Owner */ | |
| and tds.FieldId != '5DD74568-4D4B-44C1-B513-0AF5F4CDA34F' /* __Created by */ | |
| and tds.FieldId != '001DD393-96C5-490B-924A-B0F25CD9EFD8' /* __Lock */ | |
| and tds.FieldId != '40E50ED9-BA07-4702-992E-A912738D32DC' /* Blob */ |
If you would switch from Unicorn to TDS, you can use the same scripts, you just need to change the table and alias names in scripts above. I hope it helps a bit! If you have any question or remark let me know!
Tamás Tárnok 🥧 Sitecore & .NET 
Why use something you don’t already use daily as a Sitecore dev? Once you serialized your stuff with unicorn and reserialized it on a fresh instance, you can just connect tds to that instance and you should see no changes when the sync is complete. Your approach is probably working just fine, but in the future the DBs might change and TDS should be updated to handle it if it needs to.
LikeLike
Why use something you don’t already use daily as a Sitecore dev? Once you serialized your stuff with unicorn and reserialized it on a fresh instance, you can just connect tds to that instance and you should see no changes when the sync is complete. Your approach is probably working just fine, but in the future the DBs might change and TDS should be updated to handle it if it needs to.
LikeLike
Your suggested approach actually more easy, I should have used this instead of writing SQL scripts. Thank you!
LikeLike