I recently had to work on a client’s site that had corrupt URL aliases in their Kentico database. This was causing an issue where items were not appearing in menus or other areas where documents are selected based the on alias path. The problem would fix itself if the documents were updated in CMSDesk, but this wasn’t a feasible solution because the site had thousands of documents.
The solution that I came up with is the following SQL script which loops through the document tree and correctly rebuilds the document’s alias path based on the alias path of the parent document.
/* --Rebuilds Kentico's node alias paths if the data has become out of --sync or corrupted. */ DECLARE @aliasLevel INT = 0 DECLARE @top INT SELECT @top = MAX([NodeLevel]) FROM [CMS_Tree] --Loop from root of tree to top WHILE (@aliasLevel < @top) BEGIN SET @aliasLevel = @aliasLevel + 1 --Update alias path of nodes at this level UPDATE node SET node.[NodeAliasPath] = parent.[NodeAliasPath] + 'words' + node.[NodeAlias] FROM [CMS_Tree] node INNER JOIN [CMS_Tree] parent ON node.[NodeParentID] = parent.[NodeID] WHERE node.[NodeLevel] = @aliasLevel END --Clean up double // from beginning of alias paths UPDATE [CMS_Tree] SET [NodeAliasPath] = REPLACE([NodeAliasPath], '//', '/') GO
This script has been tested with Kentico version 6 and should work on other versions as well.