Migration Glitch in SharePoint Portal Server

An architectural firm was considering using Microsoft SharePoint Portal Server 2003 to help coordinate projects among their clients. Before making the commitment, the firm's partners wanted to see SharePoint Portal Server in action. So, as proof of concept, I set up the trial version of SharePoint Portal Server and Windows SharePoint Services (which Windows Server 2003 includes) on one server and installed Microsoft SQL Server 2000 on a separate server. In general, I try to avoid trial versions of software because inevitably valuable data used to test the trial version must be migrated to the production version of the software. But in this case, using the trial version of Share-Point Portal Server was unavoidable.

After identifying the SharePoint Portal Server functionality the firm needed, I set up SharePoint Portal Server, including a prototype site to assist with project coordination. After a brief demonstration to the partners, they decided to purchase new hardware and SharePoint Portal Server. They also decided to use SQL Server 2005 rather than SQL Server 2000 as the back-end database because of its performance and reporting improvements.

Now I was faced with migrating the data from the two test servers running the trial version of Share-Point Portal Server and SQL Server 2000 to the two target servers running the production version of SharePoint Portal Server and SQL Server 2005. I first attempted to use the SharePoint Portal Server's built-in backup utility to back up the data on the test SharePoint server and SQL Server and restore the data on the target SharePoint server. (The Share-Point backup includes all the portal data from SQL Server, so a separate backup of the SQL Server database isn't necessary. As an extra precaution, you could back up the SQL Server database separately.) However, when I tried to perform the restore, I received an error message that stated the service pack versions didn't match. The test SharePoint server was running Service Pack 1 (SP1), whereas the target SharePoint server was running SP2. Evidently, database schema changes were made to SP2 and the SQL Server backend, which means you need the same service pack level on the backup and target server in order to use the built-in backup utility.

I downloaded SP2 and tried to install it on the trial version of SharePoint Portal Server, but I received an error that stated I didn't have the correct version of Share-Point Portal Server. Now I was stuck. To use SQL Server 2005 as the back-end database, SharePoint Portal Server requires SP2, but I needed SP1 to restore my backup. I briefly considered using SP1 on the target SharePoint server, but I decided against it after I read numerous warnings about the unpredictable behavior when using SharePoint Portal Server SP1 with SQL Server 2005. I discovered, however, that I was able to install SP2 for Windows SharePoint Services on the test SharePoint server.

When I was searching the Internet and newsgroups for a solution, I found that many other people who installed the test version of Share-Point Portal Server with SQL Server 2000 and wanted to upgrade to the production version of SharePoint Portal Server with SQL Server 2005 were experiencing the same problem. (Note that if you're already running a production version of SharePoint Portal Server and want to use SQL Server 2005 as the back end, you can simply install SPS Service Pack 2 and migrate the data to the new server.) So, I thought I'd share the steps I performed to get the SharePoint data migrated.

After I performed these steps, I ran a few quick tests. All the data was successfully migrated. Although this solution involves a lot of steps, the whole process took only about 2 hours. The difficult part was developing a strategy that addressed the problem of not being able to update the trial version of SharePoint Portal Server with SP2. I'm not sure whether this is the only method of migrating the trial version of Share-Point Portal Server and SQL Server 2000 to the production version of SharePoint Portal Server and SQL Server 2005, but it worked for me.