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.

  1. Write down the versions of SharePoint Portal Server and Windows SharePoint Services installed on the test server by accessing the support information in the Control Panel Add/Remove Programs applet.
  2. Using SharePoint Portal Server's backup utility, back up the data on the test server. (This backup will include all of the relevant SQL Server data.)
  3. Uninstall the trial version of SharePoint Portal Server and Windows SharePoint Services on the test server.
  4. Install the production version of SharePoint Portal Server and Windows SharePoint Services on the test server. (Technically, this breaks the Microsoft licensing agreement, but I retired the test server as soon as I migrated the data to the new server.)
  5. Install SP1 for SharePoint Portal-Server and Windows SharePoint Services on the test server.
  6. Check the versions of Share-Point Portal Server and Windows SharePoint Services installed on the test server by accessing the support information in the Control Panel Add/Remove Programs applet. These versions should match the versions you wrote down in step 1.
  7. On the test server, restore the data from the backup.
  8. Install SP2 for SharePoint Portal-Server and Windows SharePoint Services on the test server.
  9. Back up the SharePoint Portal Server data on the test server.
  10. Restore the backup on the new production target server.

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.