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.