SharePoint 2010 Content Database Migration, Re-Deploy Results in Foreign Key Constraint Error

Short Description: In an upgrade from WSS 3.0 to SharePoint 2010 via the content database attach/re-attach with PowerShell upgrade, a content database can be attached and upgraded only once.  A second attempt results in the following errors:

SharePoint log:

[powershell] [SPUpgradeSession] [ERROR] [12/9/2010 11:49:06 AM]: Cannot upgrade [SPSite Url=http://sitename/site].
[powershell] [SPUpgradeSession] [DEBUG] [12/9/2010 11:49:06 AM]: Skip upgrading [SPSite Url=http://sitename/site].
[powershell] [SPUpgradeSession] [DEBUG] [12/9/2010 11:49:06 AM]: Disposing SPSite Url=http://sitename/site.
[powershell] [SPUpgradeSession] [ERROR] [12/9/2010 11:49:06 AM]: CanUpgrade [SPSite Url=http://sitename/site] failed.
[powershell] [SPUpgradeSession] [ERROR] [12/9/2010 11:49:06 AM]: Exception: Object reference not set to an instance of an object.
[powershell] [SPUpgradeSession] [ERROR] [12/9/2010 11:49:06 AM]:    at Microsoft.SharePoint.Upgrade.SPSiteSequence.get_CanUpgrade()
   at Microsoft.SharePoint.Upgrade.SPUpgradeSession.CanUpgrade(Object o)

Windows Event Log

Date  PowerShell.exe (0x0938)                  0x164C SharePoint Foundation          Database                       5586 Critical Unknown SQL Exception 547 occurred. Additional error information from SQL Server is included below.  The DELETE statement conflicted with the REFERENCE constraint "FK_Dependencies1_Objects". The conflict occurred in database "SharePoint_Config", table "dbo.Dependencies", column 'ObjectId'.  The statement has been terminated. 695f52b2-6e6d-4b5e-97d9-906e71cedd0d
 The DELETE statement conflicted with the REFERENCE constraint "FK_Dependencies1_Objects". The conflict occurred in database "SharePoint_Config", table "dbo.Dependencies", column 'ObjectId'.  The statement has been terminated. 695f52b2-6e6d-4b5e-97d9-906e71cedd0d

Problem:
I reported this to Microsoft and they confirmed they can re-produce the issue.  More information will be forthcoming.

Steps to Reproduce:

WSS Environment:
2 Web Front Ends, 1 Application Server -- WSS 3.0 12.0.0.6535
Database Server -- SQL 2005 sp3 9.0.4053

SharePoint 2010 Environment:
2 Web Front Ends, 2 Application Servers -- 14.0.5128.5000
Database Servers (mirrored in this case, but can be re-produced on standalone dbs) -- SQL 2008 R2 10.5.1746

First upgrade:

1. Take a backup of the WSS 3.0 content database in SQL 2005 SP3
2. Restore backup of the WSS 3.0 content database to the SQL 2008 R2 server associated with SharePoint 2010 environment (you can change the compatibility level without affecting the reproduceability of the problem).
3. From the SharePoint 2010 application server in the SharePoint Management Shell (Powershell) run the following command to upgrade the database:
• Mount-SPContentDatabase -Name -DatabaseServer -WebApplication -Updateuserexperience
4. Upgrade completes successfully with errors – site collections render in SP2010 look and feel and are fully working

 Second upgrade (this is the one with the issue):

1. In SharePoint 2010 environment, remove content database from web application in Central Admin
2. In SQL 2008 R2, drop content database
3. Take another backup of the original WSS 3.0 database in SQL 2005 SP3
4. Restore that backup of the WSS 3.0 content database to SQL 2008 R2 associated with the SharePoint 2010 environment.
5. From the SharePoint 2010 application server in the SharePoint Management Shell (Powershell) run the following command to upgrade the database:
• Mount-SPContentDatabase -Name -DatabaseServer -WebApplication -Updateuserexperience
6. Upgrade completes successfully with errors.   Site collections now render in the WSS 3.0 look and feel or will not render at all and throw errors when working with the site.

SharePoint Upgrade log contains the errors

 [powershell] [SPUpgradeSession] [ERROR] [12/9/2010 11:49:06 AM]: Cannot upgrade [SPSite Url=http://sitename/site].
[powershell] [SPUpgradeSession] [DEBUG] [12/9/2010 11:49:06 AM]: Skip upgrading [SPSite Url=http://sitename/site].
[powershell] [SPUpgradeSession] [DEBUG] [12/9/2010 11:49:06 AM]: Disposing SPSite Url=http://sitename/site.
[powershell] [SPUpgradeSession] [ERROR] [12/9/2010 11:49:06 AM]: CanUpgrade [SPSite Url=http://sitename/site] failed.
[powershell] [SPUpgradeSession] [ERROR] [12/9/2010 11:49:06 AM]: Exception: Object reference not set to an instance of an object.
[powershell] [SPUpgradeSession] [ERROR] [12/9/2010 11:49:06 AM]:    at Microsoft.SharePoint.Upgrade.SPSiteSequence.get_CanUpgrade()
   at Microsoft.SharePoint.Upgrade.SPUpgradeSession.CanUpgrade(Object o)

 Windows Event Log contains the error

Date  PowerShell.exe (0x0938)                  0x164C SharePoint Foundation          Database                       5586 Critical Unknown SQL Exception 547 occurred. Additional error information from SQL Server is included below.  The DELETE statement conflicted with the REFERENCE constraint "FK_Dependencies1_Objects". The conflict occurred in database "SharePoint_Config", table "dbo.Dependencies", column 'ObjectId'.  The statement has been terminated. 695f52b2-6e6d-4b5e-97d9-906e71cedd0d
 The DELETE statement conflicted with the REFERENCE constraint "FK_Dependencies1_Objects". The conflict occurred in database "SharePoint_Config", table "dbo.Dependencies", column 'ObjectId'.  The statement has been terminated. 695f52b2-6e6d-4b5e-97d9-906e71cedd0d

When looking at the database it is clear that the GUID referenced appears in the objects and dependencies table only when the site collection is attached.  When it is removed the GUID no longer appears in either table, so it's not immediately clear why a delete statement would cause a foreign key constraint violation on a guid that should no longer exist.

Microsoft have confirmed that this is an issue with the PowerShell method of upgrading to SharePoint 2010, and that the workaround is to use stsadm.  Since PowerShell is Microsoft's recommended tool for administering SharePoint 2010 and according to this technet article Microsoft's position is, 

We recommend that you use Windows PowerShell when performing command-line administrative tasks. The Stsadm command-line tool has been deprecated, but is included to support compatibility with previous product versions.

I requested that there be a fix to the PowerShell method of upgrading a WSS 3.0 content database to SharePoint 2010, and I'm excited to hear of a fix soon!


Microsoft's workaround (which I haven't tried but which I'm providing in case you're really stuck and can't wait for a fix -- is below).
Instead of the PowerShell command, run
stsadm -o addcontentdb -url yoursiteurl -databasename yourdatabaseame -preserveolduserexperience false

My Workaround:
For us, this is a problem primarily with the prep for a production rollout, and not with the production rollout itself (unless something goes horribly wrong and you have to retract and re-upgrade the same site collection twice).  To workaround (actually avoid is more appropriate), you can take advantage of technologies like cloning and snapshots.
Option 1:  Prior to each test run, clone your environment (this is only really feasible if you're using virtual servers) either with a clone or a snapshot, and reset the environment before each test upgrade.
Option 2:  Create a gold point in time "image" of your environment and revert all servers back to that point in time after each test run.