InvalidDatabaseSchema failed during SharePoint PreUpgradeCheck

In service pack 2 for Microsoft Office SharePoint Server 2007 a new operation called PreUpgradeCheck was introduced for the STSADM command.  This operation uses the Microsoft Best Practices Analyzer to identity any issues that will need to be resolved prior to upgrading to SharePoint 2010.

Running the command is simple.  At a command prompt type:  stsadm –o preupgradecheck

This command will not make any modifications to your SharePoint farm.  Once the command is complete you will be presented with a nice HTML report.  The report contains a lot of great information and should probably be stored with your disaster recovery plan.

During a recent run of this command on a production server I received a “Failed” message on the InvalidDatabaseSchema check.   This means that the database schema for one of the content databases did not match the original SharePoint schema.    I was very surprised to see this since no one except for the DBAs had access to the server and no one should have been modifying the databases directly.

I opened up the LOG directory in the SharePoint hive and located the latest PreUpgradeCheck log file.  About 1/2 way down the log I found an entry related to the specific error.  It indicated that an extra table called “sysdiagrams” was found in the content database.    I immediately had my ah-ha moment.   Someone who had access to the server either accidentally or intentionally went into the Database Diagrams folder for the content database in SQL Server Management Studio (SSMS).  When they did this SSMS stated that one or more objects were missing that were required to use database diagramming and asked if they should be created.   Clicking “yes” would immediately create the sysdiagrams table.

To resolve the issue and return the database schema back to the original state I user SSMS and opened up the System Tables section of my content database and deleted the sysdiagrams table.    I went back and ran the PreUpgradeCheck and this time I got a clean report. 

Note: Do not make any modifications to database tables unless you have a full backup and you are confident you could recover in case of an unexpected issue.  Making modifications directly to SharePoint database tables which change the schema from the out of the box configuration may invalidate any support agreements you have with Microsoft.

0 thoughts on “InvalidDatabaseSchema failed during SharePoint PreUpgradeCheck”

  1. Thanks for posting this Mike! Apparently preupgradecheck assumes that things like security, separation of duties and change controls were in place. The resolution makes perfect sense and poses no risk to SharePoint.

  2. I am getting the same error when I run the preupgradecheck. Our company uses an old version of SQL 2000 and looking to upgrade from WSS 3.0 to SP 2010. I”ve looked all over, and I can”t find the sysdiagrams table in our current environment. Does anyone have any suggestions on another possible solution? Or could the fact that the sysdiagrams table is missing be the possible culprit? Thanks!

    1. I would recommend that you closely review the preupgradecheck log file. It should detail what the specific issue is. As mentioned in this post, the log pointed to my specific issue which was the sysdiagrams table being created.

Leave a Reply