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

View the code on Github

This script has been tested with Kentico version 6 and should work on other versions as well.