SharePoint 2007: How to Delete an Orphan from Configuration Database

We attempt to attach a content database using below command

STSADM.EXE -o addcontentdb -url  https://sush07 -databasename WSS_Content –databaseserver Peaches

Result:

The attach operation cannot continue because another object in this farm already contains the same ID. Each object in a farm must have a unique ID. In order to proceed with the attach operation you must assign a new ID to this database. To attach this database with a new ID, use the “stsadm.exe —o addcontentdb” operation with the —assignnewdatabaseid parameter. Note that if this new database and an existing database contain the same site collections, attaching this database will likely result in orphaned site collections due to conflicts between the two databases.

Now attempt to attach the database using -assignnewdatabaseid parameter

STSADM.EXE -o addcontentdb -url https://sush07 -databasename Wss_Content –databaseserver Peaches -assignnewdatabaseid

Result:

A SharePoint database named already exists. You must supply another name for the new database.

Step 1:

As the above error indicates that a database already exists to confirm, log on SQL Server and open SQL Server Management Studio and execute the below query against the SharePoint Configuration Database.

select Name, ID from Objects where Properties like '<object type="Microsoft.SharePoint.Administration.SPContentDatabase%'

After executing the above query, search for the database and it would be listed.

We can use stsadm –o deleteconfigurationobject however, executing it on production farm isn’t supported until prior approval from Microsoft.

OR

Step 2:

Install PowerShell in Windows 2003

PowerShell on MOSS 2007

Install : https://support.microsoft.com/kb/968930 (No Restart Required)

Run PowerShell and then execute: Set-ExecutionPolicy UnRestricted

Press Y

Before programming against the SharePoint and/or MOSS 2007 object model the appropriate assemblies must be loaded. The following commands do this:

[System.Reflection.Assembly]::Load(“Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c”)

[System.Reflection.Assembly]::Load(“Microsoft.SharePoint.Portal, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c”)

Step 3:

Copy the below code in notepad and save it as orphan.ps1 and save it on C:\

[void][system.reflection.assembly]::LoadWithPartialName('Microsoft.sharepoint')

$farm = [microsoft.sharepoint.administration.spfarm]::Local

$dbservice = $farm.Services | where { $_.typename -eq 'Windows SharePoint Services Database' }

$instance = $dbservice.Instances | select -First 1

$dbs = $instance.Databases

$Property = [Microsoft.SharePoint.Administration.SPContentDatabase].GetProperty("DisplayName")

foreach ( $db in $dbs )

{

    $Property.GetValue($db, $null)

}

To execute the above script type . .\orphans.ps1 within PowerShellthis would listed all the databases.

Step 4:

Copy the below code in notepad and save it as DeleteOrphan.ps1 and save it on C:\

Note: Change the name of the database

$DbToDelete = "WSS_Content"

foreach ( $db in $dbs )

{

    $value = $Property.GetValue($db, $null)

     if ( $value -eq $DbToDelete) { $targetdb = $db }

}

$delete = [Microsoft.SharePoint.Administration.SPContentDatabase].GetMembers() | Where { $_.name -eq "delete" }

$delete.Invoke($targetdb, $null)

To execute the above script type . .\deleteorphan.ps1 within PowerShellthis would delete the reference of the specified databases from configuration database.

Re-execute the SQL query as per Step 1 and confirm that we don’t see the database listed.

Later, try to attach the database

STSADM.EXE -o addcontentdb -url https://sush07 -databasename WSS_Content –databaseserver Peaches

Result:

Operation Completed Successfully.