Extract Project Web Access site data to a new content database

  • Article

This Office product will reach end of support on October 10, 2017. To stay supported, you will need to upgrade. For more information, see , Resources to help you upgrade your Office 2007 servers and clients.

 

Topic Last Modified: 2015-03-09

This article describes how to extract a Project Web Access (PWA) site from an existing content database that also contains non-Project Web Access sites, and then move the Project Web Access site data to a new content database. This procedure is a helpful workaround when you are applying software updates for Microsoft Office SharePoint Server 2007 or Windows SharePoint Services 3.0 on your Microsoft Office Project Server 2007 farm, and your PWA site exists on a content database that also contains numerous non-project sites.

We highly recommend that when you are deploying Project Server 2007 you create a content database that is used exclusively for your Project Web Access site data. This can include sub-sites that might exist under the Project Web Access site, such as Project workspaces. Having a content database solely for your Project Web Access site data is a recommended best practice when you are deploying Project Server 2007. This creates a cleaner configuration and reduces the chance of problems when you are executing other operations. Examples of such operations include backup and recovery, migrating data to a new farm, migrating to a newer version of Project Server, and, as noted earlier, updating components on the farm.

When you are applying Windows SharePoint Services 3.0 or Microsoft Office SharePoint Server 2007 updates to a farm, detach content databases (except any containing Project Web Access site data) from the farm before applying the update. We recommend this action as a best practice for reducing downtime. This is especially helpful if your farm contains many sites. If a content database contains your Project Web Access site and numerous other non-project sites, as a workaround you can use the process documented in this article to move the Project Web Access site to a new content database. After completing this procedure, you can then keep the content database containing the PWA site attached to the farm, and then detach all other content databases before updating the farm.

Important

Do not detach a content database from your farm that contains Project Web Access site data. Doing so might cause substantial and irrecoverable loss of your Project Web Access site data.

After completing the procedure documented in this article, the original content database you started with now will be separated into two content databases:

  • The original content database, which contains all of the original data that you started with before beginning this process, except for the Project Web Access site and all sub-sites that are contained under it.

  • The new content database , which contains only the Project Web Access site and all sub-sites that are contained under it.

This procedure should not delete any data that originally existed in the content database. The goal is to remove the Project Web Access site and its sub-sites from the original content database, move it to its own content database, and successfully reconnect it to the farm.

Overview

The procedures documented in this article require you to install a temporary Project Server 2007 farm ("Test Farm") in which some of the steps must be completed.

Important

Make sure that the Project Server 2007 Test Farm is at the same application and patch level as the production farm, because databases will be moved back and forth between farms.

The following table describes steps that need to be completed on each farm and the sequence in which they need to be completed:

Project Server 2007 farm Project Server 2007 test farm

1. Identify the content database that contains the Project Web Access site.

2. Create a backup copy of the content database that contains the Project Web Access site.

3. Ensure that users do not access the Project Web Access site or its workspaces.

4. Restore the backup copy of the content database on the computer that is running SQL Server for the test farm.

5. Attach the content database to the test farm.

6. Inventory the sites contained on the content database.

7. Delete all sites except for the Project Web Access site from the content database.

8. Create a backup copy of the content database.

9. Restore the backup copy from the test farm to the computer that is running SQL Server for the original farm using a new name.

10. Stop the Project SSP Timer Job on the farm.

11. In the original content database, delete the Project Web Access site.

12. Attach the restored content database (the one from the test server) to the farm.

13. Open the Project Web Access site to verify that it is functional.

14. Start the Project SSP Timer Job on the farm.

15. Secure the PWA site content database.

Identify the content database that contains the Project Web Access site

A Project Server 2007 farm can contain many content databases, and it may not be readily apparent to you which one contains your Project Web Access site. If you do not know which content database contains the Project Web Access site, you can use the following general steps to identify it:

  1. Check your Project Web Access site and verify your Project Web Access site name (for example, for the URL http://contoso/MyPWA, the PWA site name is ‘MyPWA’.).

  2. Generate a list of the content databases for the Web application used by the site URL. You can use the following Stsadm command to create this list:

    Stsadm -o enumcontentDBs -url <WebAppURL>

    For example: Stsadm -o enumcontentDBs -url http://pserv100:81

    Note

    For information about this command, see Enumcontentdbs: Stsadm operation (Office SharePoint Server).

  3. Search each content database identified in the previous step for the Project Web Access site. You can use the following SQL query (in this example, MyPWA is the Project Web Access site name):

    USE [<WSSContentDBName>]SELECT COUNT(*) from webs where fullUrl like ‘MyPWA’

  4. The results should provide the content database used for the Project Web Access site.

Back up the content database containing the Project Web Access site

On the computer that is running SQL Server used by this farm's databases, create a backup copy of the content database used for the Project Web Access site. This site should have been identified in the results from the SQL query run in the previous step.

Important

At this point, ensure that no one can write any content under the Project Web Access site collection. Note that this restriction includes not only the Project Web Access site, but also any sub-sites that exist under the site (for example, Project Workspaces). This restriction will prevent any write operations from occurring to the farm databases.

Restore the content database on the computer that is running SQL Server used for the "Test Farm"

As mentioned previously in the overview, a Project Server 2007 “Test Farm” should have been created in order to complete the procedures in this article, and this farm must be at the same application and patch level as the production farm. This step requires that on the computer that is running SQL Server used for the Test Farm, you must restore the content database from the backup copy that was created in the previous step. Note that the backup copy was created on the original farm, but you are restoring the backup to the Test Farm.

Attach the content database to the Test Farm

After restoring the content database to the Test Farm, you must attach it to the Test Farm. You can do this through the SharePoint Central Administration Web site by using the following steps:

  1. On the test farm’s Central Administration page, click Application Management.

  2. On the Application Management page, in the SharePoint Web Application Management section, click Content Database.

  3. On the Manage Content Database page, click Add a content database.

  4. On the Add Content Database page, in the Database Name and Authentication section, in the Database Server field, type the name of the database server hosting the test farm. In the Database Name field, type the name of the content database.

  5. Click OK.

You can also choose to attach the content database by using the Stsadm addcontentdb operation. For example:

Stsadm -o addcontentdb -url http://testfarm:80 -databaseServer <SQLServerName> -databaseName <NewContentDBName>

For example: Stsadm -o addcontentdb -url http://testfarm:80 -databaseServer ContosoNetwork\SQLServer1 -databaseName Content_database_PWA

Note

For more information, see Addcontentdb: Stsadm operation (Windows SharePoint Services).

Inventory the sites on the content database

You will need to find out the sites that are on the content database. You can use the Stsadm -o enumsites operation to generate a list of all sites on the content database.

Stsadm -o enumsites -url <URL>

For example: Stsadm -o enumsites -url http://testfarm:80

Note

For more information, see Enumsites: Stsadm operation (Windows SharePoint Services).

Delete all sites except the Project Web Access site from the content database

Now that you have identified all sites on the content database, you can delete all of them except for the Project Web Access site. All of the sites that you delete will still exist on the original farm in the original content database. The goal is to only have the Project Web Access site and its sub-sites remaining on the content database.

You can use the Stsadm –o deletesite command to delete these sites:

Stsadm -o deletesite -url <URL>

For example: Stsadm -o deletesite -url http://SharePointSite1

Note

For more information, see Deletesite: Stsadm operation (Windows SharePoint Services).

You can use this command to write an automated script that references the list generated by the enumsites operation in the previous step. (But make sure to remove the Project Web Access site from the list.)

Running this command to remove the non-Project sites leaves the content database containing only the Project Web Access site and all sub-sites under it.

Create a backup copy of the content database on the test farm

After deleting the non-Project Web Access sites from the content database on the test farm, the content database should only contain the Project Web Access site and any sub-sites that exist under it. In SQL Server, create a backup copy of this content database.

Restore the content database on the computer that is running SQL Server used for the original farm

Move the backup copy you made on the test farm to the computer that is running SQL Server that hosts the original farm. In SQL Server, restore the backup to SQL Server.

Important

When restoring the content database to the original farm, make sure to restore the file with a new database name.

Disable the Project SSP Timer job on the farm

Before you attempt to delete the Project Web Access site from the original content database (the next step), you must disable the Project SSP Timer job through by using Central Administration.

Important

You must remember to disable this job before attempting to delete the Project Web Access site from the original content database. Failure to disable the job before deleting the site can result in data loss.

  1. In Central Administration, on the Operations page, in the Global Configuration section, click Timer job definitions.

  2. On the Timer Job Definitions page, in Title the column, click Project Server Synchronizing Job for <ssp_title> (where "ssp_title" is the name of the Shared Services Provider for your Project Web Access instance).

  3. On the Edit Timer Job page, click Disable.

Delete the Project Web Access site from the original content database

You must delete the Project Web Access site from the original content database before you attach the new content database to the farm. The new content database will contain the Project Web Access site hereafter.

You can use the Stsadm –o deletesite command to delete the Project Web Access site:

Stsadm -o deletesite -url <URL>

For example: Stsadm -o deletesite -url http://Contoso/myPWA

When you run this command, the Project Web Access site and all its subsites is deleted. The content database and all other sites that were originally onit will otherwise be left untouched.

Attach the new content database to the farm

After you restore the new content database that contains only the PWA site to the farm in a previous step, it should now be available to attach to the farm. You can do this through Central Administration by using the following steps:

  1. On the farm’s Central Administration page, click Application Management.

  2. On the Application Management page, in the SharePoint Web Application Management section, click Content Database.

  3. On the Manage Content Database page, click Add a content database.

  4. On the Add Content Database page, in the Database Name and Authentication section, in the Database Server field, type the name of the database server hosting the farm. In the Database Name field, type the name of the content database.

  5. Click OK.

You can also choose to attach the content database by using the following Stsadm addcontentdb operation:

Stsadm -o addcontentdb -url <http://originalfarm:80> -databaseServer <SQLServerName> -databaseName <NewContentDBName>

For example: Stsadm -o addcontentdb -url http://Contoso:80 - databaseServer ContosoNetwork\SQLServer1 - databaseName Content_database_onlyPWA

Note

For more information about this command, see Addcontentdb: Stsadm operation (Windows SharePoint Services).

Open the Project Web Access site to verify that the site can be browsed

After adding the content database to the farm, attempt to open the Project Web Access site to verify that the site can be browsed.

Enable the Project SSP Timer job on the farm

After verifying that the Project Web Access can be browsed, you can now enable the Project SSP Timer job that you disabled in a previous step.

Important

You must remember to enable this job. Leaving this job in a disabled state can cause problems and will result in an unsupported operating condition.

  1. In Central Administration, in the Operations page, in the Global Configuration section, click Timer job definitions.

  2. On the Timer Job Definitions page, in the Title column, click Project Server Synchronizing Job for <ssp_title> (where "ssp_title" is the name of the Shared Services Provider for your Project Web Access instance).

  3. On the Edit Timer Job page, click Enable.

Secure the PWA content database

You must ensure that the content database containing the Project Web Access site cannot have any additional sites saved to it. You can secure the content database through the following steps:

  1. In Central Administration, in the Application Management page, in the SharePoint Web Application Management section, click Content databases.

  2. On the Manage Content Databases page, check the properties of the content database that contain the PWA site. Make sure that the value for Current Number of Sites is the same value for Maximum Number of Sites

    Note

    If the Project Web Access site is the only site on this content database, the value of Current Number of Sites should be "1".

  3. Click Save.

By making the Current Number of Sites value the same as the Maximum Number of Sites value, you ensure that no additional sites will be added to the content database.

Conclusion

After you complete all the procedures in this article, you will have the Project Web Access site data on a single content database. Any additional subsis that you create under the site will also be stored on the content database.

The content database that contains the Project Web Access site must not be detached from the farm. When you apply Windows SharePoint Services 3.0 or Office SharePoint Server 2007 updates, we recommend that you detach the farm content databases, except for this one. Because the Project Web Access site is the only site contained on the attached content database, it should not significantly increase the amount of time needed to complete the update.