Last active
March 18, 2018 00:49
-
-
Save nullydragon/1f390e529e37993ac1f087db8a41ac9c to your computer and use it in GitHub Desktop.
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
--Script for umbraco database, useful if you are having media/file problems | |
--This will remove duplicated null entries in the cmsPropertyData table | |
--Clean Up Content Properties for Media (Id=6) and Files (Id=24) | |
--Work through these id's one by one | |
DECLARE @propertyTypeId INT; | |
--File | |
SET @propertyTypeId = 24 --umbracoFile | |
--SET @propertyTypeId = 25 --umbracoExtension | |
--SET @propertyTypeId = 26 --umbracoBytes | |
--SET @propertyTypeId = 180 -- title | |
--SET @propertyTypeId = 189 -- caption | |
--Media | |
--SET @propertyTypeId = 6 --umbracoFile | |
--SET @propertyTypeId = 7 --umbracoWidth | |
--SET @propertyTypeId = 8 --umbracoHeight | |
--SET @propertyTypeId = 9 --umbracoBytes | |
--SET @propertyTypeId = 10 --umbracoExtension | |
--SET @propertyTypeId = 48 -- caption | |
DELETE FROM cmsPropertyData WHERE Id IN --uncomment to delete records | |
( | |
--Get the damaged records version and node id | |
SELECT | |
--[versionId], contentNodeId, COUNT(*) AS Number, Max(Id) as IdToKeep,--uncomment this line to see details of broken records | |
Min(id) AS IdToDelete | |
FROM [cmsPropertyData] | |
WHERE | |
Propertytypeid = @propertyTypeId | |
AND versionId IN | |
( | |
SELECT | |
versionId | |
FROM cmsPropertyData | |
WHERE (dataNtext IS NULL AND dataDate IS NULL AND dataDecimal IS NULL AND dataInt IS NULL AND dataNvarchar IS NULL) | |
AND propertytypeid = @propertyTypeId | |
) | |
GROUP BY versionId, contentNodeId | |
HAVING COUNT(*) > 1 | |
) | |
Hey, no tickets that I am aware of, and not certain what causes the issue, Its possible that its due to a failed umbraco upgrade, but I'm not 100% certain. It was a nightmare to track down what was going on with the site that lead to this script being written.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
hi - is there a ticket on http://issues.umbraco.org/issues that this script is for? i.e. do you know what caused the duplicates in the first place?
thanks