INTRODUCTION

We just completed a MOSS server rebuild project which we managed to align both Production and Test environments with exactly the same topology and configurations. We installed using the exactly the same user accounts, configurations and even the list of features and solutions. After we configured the farm, we then took Production's latest database backup and restore it in Test. Everything worked perfectly except one thing:

The manual links in navigation menu items are not updated.

You know that in MOSS you can go to Site Actions -> Site Settings -> Modify Navigation, right? And we can create external/manual links, correct? The problem is, some of the users have created links that point to a document/page within MOSS but using static URL instead of relative. The problem is, these manual/static links are NOT updated after database restore to a different environment.

For example:
http://intranet/Pages/Home.aspx is meant to be updated to http://test-intranet/Pages/Home.aspx.

This will create a huge risk because we've actually had problem before when user thought they're still in Test but they're actually in Production. And guess what...he deleted a site that he thought was in Test.

SOLUTION

Anyway, to cut the long story short, I've managed to come up with a very simple SQL statement (which some of you must have known anyway) that can allow us to modify static links into relative links:

update navnodes set url = REPLACE ( url , 'http://intranet' , '' ) where url like '%http://intranet%'

The above statement will update ALL static links in navnodes table (which is the table used by MOSS navigation control) and remove any static reference to any page/document within MOSS.

WHAT LINKS ARE UPDATED BY DB RESTORE?

- Links within content query web-part or page content field

- Automatic links that are created by MOSS in navigation items as a result of creating a page/sub-site

WHAT LINKS ARE NOT UPDATED?

- Manual/static links in MOSS Navigation Items although they may point to a page/document within Sharepoint itself

Hope this helps,
Tommy