Changing absolute urls in SharePoint

If you have an instance of SharePoint (2003 or MOSS 2007 and probably 2010 though I have not tried it)that you want to move from prod to a QA or Dev environment there are sometimes absolute urls in the data. It is important to change these to your QA or Dev url otherwise it is easy to think you are testing Dev and next thing you realize you are on Production because you clicked a link that took you there and you did realize it.

WARNING: I MUST first tell you these are NOT SUPPORTED or ALLOWED by Microsoft and you should proceed with caution and please test this on a non-production environment first. By directly modifying the SharePoint Content Database you are breaking the rules set in place by MS and it is possible MS would not help you if you have made any of these changes. I of course assume no responsibility for any harm caused by this code.

I have personally used these on production environment after testing that the changes didn’t break anything, but this was my environment and is by no means a representation of your environment.

Use these queries to get an idea of what urls you may want to change. The is especially important if you have references to other production environments or even other instances of SharePoint.

-- Research Queries
select distinct SiteUrl from SchedSubscriptions
select distinct Url from NavNodes order by 1
select distinct tp_CopySource from AllUserData
select distinct SiteUrl from ImmedSubscriptions
select distinct nvarchar4 from AllUserData where nvarchar4 not like '%@%' and nvarchar4 not like '% %' order by 1

Once you have figured out what you want to replace, modify the scripts below to work for your situation. In general you can just change the two variables, but best if you review before executing.

-- Run these statements to update urls that are absolute
Declare @OldHostUrl as nvarchar(512)
Set @OldHostUrl = 'http://mysharepoint' -- SharePoint Prod

Declare @NewHostUrl as nvarchar(512)
Set @NewHostUrl = 'http://mysharepointdev -- SharePoint Dev

Update Webs set SiteLogoUrl = Replace(SiteLogoUrl, @OldHostUrl, @NewHostUrl) where SiteLogoUrl like @OldHostUrl + '%'
Update SchedSubscriptions set SiteUrl = Replace(SiteUrl, @OldHostUrl, @NewHostUrl) where SiteUrl like @OldHostUrl + '%'
Update NavNodes set Url = Replace(Url, @OldHostUrl, @NewHostUrl) where Url like @OldHostUrl + '%'
Update AllUserData set tp_CopySource = Replace(tp_CopySource, @OldHostUrl, @NewHostUrl) where tp_CopySource like @OldHostUrl + '%'
Update ImmedSubscriptions set SiteUrl = Replace(SiteUrl, @OldHostUrl, @NewHostUrl) where SiteUrl like @OldHostUrl + '%'
Update AllUserData set nvarchar4 = Replace(nvarchar4, @OldHostUrl, @NewHostUrl) where nvarchar4 like @OldHostUrl + '%'

-- use this to update the navnodes (tabs) Above queries
Update Webs set CachedNavDirty = 1