I had a recent query from a colleague regarding the bulk update project site functionality in Project Server 2010. In this scenario my colleague had taken pre-configured databases from an environment at our offices and restored these 5 databases to the clients production environment. They then created some test projects / updated configuration etc. A test environment was also created for this client, part of this assignment was to rollover production to test, this included running the bulk update project sites functionality to update the test project sites. When looking at the list of previous site path web applications they could not see the clients web application in the list. All that was present were URLs from various test machines from the CPS environment, this can be seen below:

Bulk Update Project Sites:

Having seen this myself before but never had the opportunity to investigated it, I decided to look into the issue further.

Firstly I executed the MSP_Web_PWS_ReadProjectSiteBaseURLS stored procedure which I believe is called to populate the drop down menu above. This reads data from the MSP_Web_Applications table and MSP_Projects table.

Project Site Base URLs:

As you can see, the SQL output above matches the URLs displayed in the previous path drop down in the first screen shot. Notice 3 different URLs, these PWA databases have been linked to various PWA sites.

I then checked the WSTS Server UID for the project sites in the database, the test site server UID matched the http://vm662 server UID in the MSP_Web_Applications table:

I then checked the PWA site collection ID in the content database:

PWA site ID:

ID: FAB47C15-A360-4AA8-9461-D3A9713200DD

So this all linked up as expected but with the incorrect URL, http://vm662 and not the clients server URL. http://vm662 was the VM that was used at the CPS office to create this clients initial config.

I believe the reason for this issue with the URL is that when using the 5 database restore process the PWA site is not recreated. The provisioning process just links to the existing PWA site collection in the restored content database. The URL doesn’t get updated in the MSP_Web_Applications tables as the site ID remains the same so Project Server doesn’t think there is a new URL. If you take only the 4 PWA databases and create a new PWA site, the URL is displayed correctly in the scenario described above. This is just a display issue, all functionality works correctly once the bulk update project site process has run.

Hopefully that will help to explain why you might be experiencing the same issue as seen / described above.