Recreating Orphan References

Background info:

What we are essentially trying to do here is recreating the reference between ConfigDB.sitemap.ID and ContentDB.Webs.SiteID, to get rid of Orphans. In most cases this is left best for Sharepoint to do (through detaching-attaching the content DB). This creates the reference if none exist. Manually updating the references will corrupt the indexed content on these sites and search may not work.

Note: Please backup ContentDB & Config DB before even firing a query on these. Also do so after hrs, as you may lockup these tables.

Issue with multiple sites with same URL in the farm:

One of the issues we had, was that multiple sites existed with the same URL. So if users type that URL, Sharepoint has no way to uniquely identify the site being referenced. For this very reason detach-attach also does not work, nor does other stsadm tools like databaserepair (it will give you zarro results). We would need to fix this issue before trying to proceed further into recreating references.


Run the stored proc :

create proc dbo.proc_GetOrphanedSites
as
Drop table orphanlist
CREATE TABLE [dbo].[orphanlist]( [farm] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [databasename] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [SiteID] [uniqueidentifier] NULL, [sitepath] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [type] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)
drop table orphan_hopper declare @dbname as varchar(250), @cmdstr as varchar(2000), @dbid as varchar(250),@configdb as varchar(250) /** only change the following line and nothing else, change spskills_config_db to your config db name **/select @configdb = 'Sharepoint_Config'
/** Change nothing below this line **/select @cmdstr = 'select distinct b.name as ''databasename'', b.id as ''dbid'' into orphan_hopper from [' + @configdb + '].dbo.sitemap as a inner join [' + @configdb + '].dbo.objects as b on a.databaseid=b.id inner join [' + @configdb + '].dbo.objects as c on c.id=a.applicationid inner join [' + @configdb + '].dbo.objects as d on b.parentid=d.id inner join [' + @configdb + '].dbo.objects as e on d.parentid=e.id ' exec (@cmdstr) DECLARE DBCursor CURSOR For Select databasename, dbid From orphan_hopper OPEN DBCursor FETCH NEXT FROM DBCursor into @DBName, @dbid WHILE @@FETCH_STATUS =0 BEGIN INSERT INTO orphanlist([Type], farm, databasename,[sitepath], SiteID) EXEC (' select ''Potential ConfigDB orphan:'' + '''+@dbname+''' as [Type], '''+@configdb+''' as [farm], '''+@dbname+''' as [databasename],path as [sitepath], id as [SiteID] from ['+@configdb+'].dbo.sitemap where id not in (select id from ['+@dbname+'].dbo.sites) and databaseid = '''+@dbid+''' union select ''Potential ConfigDB orphan:'' + '''+@dbname+''' as [Type], '''+@configdb+''' as [farm], '''+@dbname+''' as [databasename],path as [sitepath], id as [SiteID] from ['+@configdb+'].dbo.sitemap where id not in (select siteid from ['+@dbname+'].dbo.webs where parentwebid is null) and databaseid = '''+@dbid+''' union select ''Potential ContentDB orphans:'' + '''+@dbname+''' as [Type], '''+@configdb+''' as [farm], '''+@dbname+''' as [databasename],fullurl as [sitepath], siteid as [SiteID] from ['+@dbname+'].dbo.webs where parentwebid is null and siteid not in (select id from ['+@configdb+'].dbo.sitemap where databaseid = '''+@dbid+''') union select ''Potential ContentDB orphan:'' + '''+@dbname+''' as [Type], '''+@configdb+''' as [farm], '''+@dbname+''' as [databasename],fullurl as [sitepath], siteid as [SiteID] from ['+@dbname+'].dbo.webs where parentwebid is null and siteid not in (select id from ['+@dbname+'].dbo.sites) ') FETCH NEXT FROM DBCursor into @DBName, @dbid END CLOSE DBCursor DEALLOCATE DBCursor select * from orphanlist

GO

· If there are no duplicates (URL’s) then all you need to do is attach/detach the content db (use stsadm for this, as you may experience script timeouts if you try it through CA-UI)

· If there are duplicates but belong to different databases, then you need to restore the site collections on a different path (stsadm). And then detach-attach the databases one by one.

· If they all happen to reside in the same database, then either we need to change the path associated with each of them, or figure out the one you need to restore and delete the rest (Query Analyser). Then detach-attach the content DB.