Upgrade your Internet Experience
Microsoft.com
Welcome Sign in
Project Server TechCenter
Click to Rate and Give Feedback
TechNet
TechNet Library
Office
Project Server 2007
Operations
 Migrate Project Server data from on...
Migrate Project Server data from one farm to another

Updated: 2009-05-14

This article contains information and steps to move data from a Project Web Access site associated with Microsoft Office Project Server 2007 from one Office Project Server 2007 farm to another Office Project Server 2007 farm. You might want to move the project databases and project workspaces if you are migrating from a Windows SharePoint Services farm to a Microsoft Office SharePoint Server farm, or moving from a 32-bit to a 64-bit version deployment of Office Project Server 2007.

Important Important:

This task requires you to stop the complete farm. This will make all farm sites and assets unavailable to users until the process is completed.

This article does not provide information about how to upgrade Microsoft SQL Server 2005 on the database server. For more information about that process, see Upgrading to SQL Server 2005 (http://go.microsoft.com/fwlink/?LinkId=133288) in SQL Server Books Online.

Back up and restore databases

This section covers the procedures that you must follow to complete the move process for all the databases associated with Office Project Server 2007.

Overview of the move process

In the move process, you will use Office Project Server 2007 tools and SQL Server 2005 tools. Moving all of the databases from one database server to another database server consists of the following steps:

(In some environments, the database administrator must perform certain steps. Those steps begin with "[DBA]".)

  1. Perform a full backup of the default Shared Services Provider (SSP).

  2. Completely stop the farm by quiescing it from SharePoint Central Administration and stopping the services associated with Office Project Server 2007 and by stopping the Project Server Timer Service, the Project Server Queue, and Project Server Eventing Services.

  3. [DBA] Back up the following farm databases by using SQL Server tools:

    • Draft database

    • Published database

    • Archive database

    • Reporting database

    • Content database

      Important Important:

      There may be more than one Content database in the Project Server farm.

  4. [DBA] Copy or move the backup files to the destination database server.

  5. [DBA] Restore all of the databases on the destination database server by using SQL Server tools.

  6. [DBA] Replicate all of the SQL Server logins, fixed server roles, fixed database roles, and permissions for these databases to the destination server.

Backing up databases on the source database server

Important Important:

Membership in the db_backupoperator fixed database role is the minimum required to complete this procedure.

Important Important:

It is important that the queue service is emptied and stopped to prevent data inconsistencies.

In some environments, the DBA must perform these steps.

Back up databases on the source database server

  1. Open SQL Server Management Studio and connect to the database server.

  2. In Object Explorer, expand Databases.

  3. Right-click the database that you want to back up, point to Tasks, and then click Back Up.

  4. In the Back Up Database dialog box, select the kind of backup that you want to perform from the Backup type list. For more information about which backup type to use, see Overview of the Recovery Models (http://go.microsoft.com/fwlink/?LinkId=114396) in SQL Server 2005 Books Online.

  5. Under Backup component, select the Database option.

  6. In the Backup Set section, either use the default name provided or specify a name for the backup set in the Name text box.

  7. Specify the expiration date for the backup set. This determines how long, or when, the backup set can be overwritten by any later backups with the same name. By default, the backup set is set to never expire (0 days).

  8. In the Destination section, if the correct path of the backup folder is not listed, you can add the path by clicking Add.

  9. Click OK to back up the database.

  10. Repeat steps 3-9 for each database that you want to move.

Copying or moving the backup files to the destination database server

Important Important:

Read access to the shared folder on the source computer and Write access to the shared folder on the destination computer is the minimum required to complete this procedure.

In some environments, the DBA must perform these steps.

Copy or move the backup files to the destination database server

  1. In Windows Explorer, browse to the location of the .bak files for the databases.

  2. Select the .bak files for the databases that you want to move, and then either copy or move them to the destination directory.

Restoring databases on the destination database server

Important Important:

Membership in the dbcreator fixed server role is the minimum required to complete these procedures.

You must add the Farm Administrator account to the dbcreator fixed server role to perform this procedure. The account can be removed from the role after the recovery operation is completed.

In some environments, the DBA must perform these steps.

Restore databases on the destination database server

  1. Open SQL Server Management Studio and connect to the database server.

  2. In Object Explorer, and then expand the database instance.

  3. Right-click the Databases node, and then click Restore Database.

  4. In the Restore Database dialog box, under Destination for restore, type the name of the database. You must use the identical name for the database that it had on the source database server.

  5. Under Source for restore, select the From device option.

  6. In the Specify Backup dialog box, select File from the Backup media list.

  7. Click Add, and then browse to the .bak file for the database.

  8. Select the .bak file, and then click OK. Click OK again to add the path of the Restore Backup dialog box.

  9. Ensure that the backup set is selected in the Select backup sets to restore list.

  10. In the Select a page pane, click Options.

  11. In the Restore options section, select only Overwrite the existing database. Unless the environment or policies require otherwise, do not select the other options in this section.

  12. In the Recovery state section, select the RESTORE WITH RECOVERY option.

    For more information about these recovery options, see Restore Database (Options Page) (http://go.microsoft.com/fwlink/?LinkId=114420).

  13. Click OK to complete the recovery operation.

  14. Repeat steps 3-13 for each database that you are moving.

Copying to the destination server all of the SQL Server logins, fixed server roles, fixed database roles, and permissions for these databases

For information about how to copy SQL Server roles and permissions to the destination database server, see Microsoft Help and Support article 246133, How to transfer logins and passwords between instances of SQL (http://support.microsoft.com/kb/246133). For troubleshooting information, see Microsoft Help and Support article 240872, How to resolve permission issues when you move a database between servers that are running SQL Server (http://go.microsoft.com/fwlink/?LinkId=123469).

Restarting the server

Important Important:

Membership in the Administrators group on the local computer is the minimum required to complete these procedures.

Restart the server

  1. Restart the server that is running Project Server.

  2. In the Services snap-in, ensure that the following services are started:

    • Office Project Server 2007 Timer Service

    • Project Server Queue service

    • Office Project Server 2007 Project Server Eventing service

  3. In Internet Information Services (IIS) Manager, ensure that the following Web sites are started:

    • Office Server Web services

    • All SharePoint sites

    • SharePoint Central Administration

Restore Project Web Access workspaces

Important Important:

You must add the Farm Administrator account to the dbcreator fixed server role to perform this procedure. The dbcreator and SecurityAdmin permissions are required for the farm administrator on the SQL Server.

In some environments, the DBA must follow these steps.

Restore Project Web Access

  1. Create a new Web Application on port 1 using a different name than the Project Web Access site to be restored.

  2. Using the SharePoint Central Administration Web site, delete the content database that was created with the new Web application on port 1.

  3. On the server that is running Windows SharePoint Services, enter the following commands at a command prompt:

    cd \program files\common files\microsoft shared\Web server extensions\12\bin

    Stsadm –o addcontentdb –url http://<servername>:1 –databasename <databasename>

    The <servername> placeholder is the name of the server that is running Office Project Server 2007, and the <databasename> placeholder is the name of the content database. In SharePoint Central Administration, verify that the content database is attached as expected.

  4. At a command prompt, type the following commands to get a list of all of PWA sites.

    cd \program files\common files\microsoft shared\Web server extensions\12\bin

    stsadm -o enumsubwebs -url http://<servername>:<port number>/<pwainstancename> > c:\sites.txt

    The <port number> placeholder is the port where the site resides. In this scenario, the site was created on port 1.

    The <pwainstancename> placeholder is the instance name for Project Web Access.

    ImportantImportant:

    The stsadm command will save the Sites.txt file to the root directory of drive C.

  5. Open the Sites.txt file in Notepad, and then make the changes as appropriate for the EXPORT command for your Project Web Access workspace.For each of the PWA sites listed in the newly created Sites.txt run the following command:

    stsadm -o export -url "http://<servername>/<pwainstancename>/<workspacename>" -filename "c:\<backupdir>\<workspacename>\" -nofilecompression -includeusersecurity -versions 4

    The <workspacename> placeholder is the name of the workspace, and the <backupdir> placeholder is name of the directory where you want to save a backup of the workspace.

  6. On the File menu, click Save As, name the file Export<pwainstancename>.bat, and then save the file as a text file to the following directory:

    drive:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\Bin

  7. Change the EXPORT command in the Sites.txt file to an IMPORT command. The following is an example of the IMPORT command:

    stsadm -o import -url "http://<servername>/<pwainstancename>/<workspacename>" -filename "c:\<backupdir>\<workspacename>\" -nofilecompression -includeusersecurity

  8. On the File menu, click Save As, name the file Import<pwainstancename>.bat, and then save the file as a text file to the following directory:

    drive:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\Bin

  9. Run the Export<pwainstancename>.bat file. This may take a long time if there are many workspaces.

  10. Provision a new Project Web Access site by using the same instance name and databases. Or, use a new instance name with the databases.

  11. Run the Import<pwainstancename>.bat file.

    Important Important:

    This may take a long time if there are many workspaces.

  12. Browse the Project Web Access site to make sure that the sites are functional.

    Important Important:

    If the project workspaces are not linked, you must either manually edit the URL of each project to its newly restored Project Workspace in the Project Workspaces section of PWA, or run the Project Workspace Site Relinker tool. For more information about this tool, see Project Workspace Site Relinker tool.

  13. Delete the temporary Web application in SharePoint Central Administration, and then use Internet Information Services (IIS) to verify that it was removed.

  14. Delete the restored content database on the server that is running SQL Server.

Site collection recommendations

The following are some guidelines for consideration when creating Project Web Access instances and content databases. Per Project Web Access site, we recommend:

  1. One Web application with one content database.

  2. Two site collections with separate content databases — the first collection/database for the root Project Web Access Site, and the second collection/database for Project Workspaces. This arrangement allows you to restore Project Web Access without renaming the Project Web Access site in the future.

  3. For larger organizations, we recommend several site collections — one for each department or location in order to avoid any future backup and recovery complexity.

See Also

Tags What's this?: Add a tag
Community Content   What is Community Content?
Add new content RSS  Annotations
Incorrect reference to "Project Server Timer Service"      joeproject   |   Edit   |   Show History
There is no such thing as the "Project Server Timer Service". This has caused quite a bit of confusion for me. The article states that this service can be found in Windows services snap-in, but it's clearly not there. Perhaps the intent is to reference to the synchronization timer job in MOSS instead.
Tags What's this?: error (x) Add a tag
Flag as ContentBug
Import and Export PowerShell Scripts      tlingenf   |   Edit   |   Show History
To help automate the export and import procedure descibed in this document, I have created a couple of PowerShell scripts that can help with this task.

Change entries in Bold to match your environment.

#-------------- Export Script (ExportSubWebs.ps1) ---------------------

# change the path to the SharePoint Bin folder
c:
cd "\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN"

# the name of a file that will be used to export the list of subwebs
[string]$exportfilename = "D:\sites.txt"

# list all subwebs in the site collection and export to a file
.\stsadm.exe -o enumsubwebs -url [enter the URL to your PWA Instance Here] > $exportfilename

# read in the XML produced by the previous command
[xml]$websexport = Get-Content $exportfilename

# export each subweb in the file using the last part of the URL as the name of the export folder
foreach( $web in $websexport.Subwebs.Subweb) {
[Uri]$url = new-object Uri($web)
[string]$filename = $url.Segments[$url.Segments.Length-1]
.\stsadm.exe -o export -url $web -filename [Backup Root Folder Path]\$filename -nofilecompression -includeusersecurity -versions 4
}


#-------------- Import Script (ImportSubWebs.ps1) ---------------------

# change the path to the SharePoint Bin folder
c:
cd "\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN"

# the name of a file that will be used to import the list of subwebs
[string]$exportfilename = "D:\sites.txt"

# read in the XML produced by the export script
[xml]$websexport = Get-Content $exportfilename

# import each subweb in the file using the last part of the URL as the name of the export folder
foreach( $web in $websexport.Subwebs.Subweb) {
[Uri]$url = new-object Uri($web)
[string]$filename = $url.Segments[$url.Segments.Length-1]
.\stsadm.exe -o import -url $web -filename [Backup Root Folder Path]\$filename -nofilecompression -includeusersecurity
}

Import as a Site Collection Administrator      tlingenf   |   Edit   |   Show History
When performing the import make sure that the account that you are using is a site collection administrator on the top-level PWA site.

Errors that may occur if you are not a site collection administrator may be:

  • user not found
  • access denied
  • FatalError: The file cannot be imported because its parent web <URL name of the exported subsite> does not exist
Tags What's this?: Add a tag
Flag as ContentBug
© 2009 Microsoft Corporation. All rights reserved. Terms of Use | Trademarks | Privacy Statement
Page view tracker