Project Server 2010 Database Restore (Part I)

It’s disaster recovery week here at Project Epistemology.  In this episode, I set my sights on the various techniques available to either restore or migrate Project Server to a new farm.  My next post will discuss how to use the elusive five database restore method to move PWA from one farm to another.

As Project Server 2007 aficionados may fondly remember, the four database restore technique was a mainstay of  troubleshooting and demo environments.  Essentially, the process was to backup the four main SQL databases from the source environment, restore them to the target environment, and then create a new PWA site using the restored databases.  This replicated the source environment into a virtual environment and allowed for remote troubleshooting or the development of proof of concept solutions.

In Project Server 2010, the four database solution only gets you so far.  As the Project Detail Pages are stored in the content database, you need all five databases to capture a full PWA instance:

  1. Archive
  2. Draft
  3. Published
  4. Reporting
  5. Content

As it would be, the other day I was looking into options to refresh a training environment, and I figured the five database restore would be simple and easy.  I went online and looked through all of the TechNet articles, and couldn’t find a simple, concise summary of how to move PWA from one farm to another using the five database restore solution (which is perhaps more a statement on my ability to search and/or comprehend online material than on the available content).  There are a number of posts that address similar topics, but most discuss how to migrate a 2007 instance to 2010 using the database detach/reattach method.

This posting is similar, but is written specifically to move one Project Server 2010 PWA instance from one farm to another using the database detach reattach method.

First a disclaimer…I will gladly defer to any actual SharePoint or Project Server DR experts who may point out specific steps in this process that are not required or irrelevant.  All I can say is that these steps worked for me in a virtual environment, and that’s good enough for me.  Feel free to add any and all feedback at the bottom of this post.

Solution Overview

First off, let’s look at the available solutions for moving PWA:

Central Admin Farm Backup/Restore – this is the method that I would definitely advocate if you’re trying to create a high fidelity environment that corresponds closely to your source environment.  This solution is well documented on TechNet:

In 2007 however, I often had issues with this solution when trying to replicate client data to virtual environments for troubleshooting purposes as the restore would often fail if the virtual environments weren’t patched to exactly the same level as the source environment.  If moving from PROD to DEV in a corporate environment, patches should be the same however.

Four Database Restore – This is another popular method that worked fine in 2007 and has certain limitations in 2010.  In the four database restore method, the key PWA SQL databases are restored to the target environment and then used to provision a new PWA site.  Doing so in 2010 will restore project data, but will not restore the Project Detail Pages.  Clicking on a project in the Project Center will yield an error message and send the user back to the Project Center.

Still, the four database restore method is a quick and easy way to get data into a virtual environment and is perfectly adequate to do troubleshooting on such things as security or timesheet data.  This may be my own opinion, but I’ve found this method to be more fault tolerant than the Central Admin method, i.e. it seems to work better when restoring databases across SharePoint patch levels.

Here’s the rough process:

  1. Backup the SQL databases from the source environment.
  2. Restore the SQL databases to the target environment.
  3. Provision a new PWA site in the target environment using the restored databases.

Five Database Restore – …and that brings us to the five database restore.  This restores the entire PWA instance to the target environment.  It’s a little work, but not that much harder than the four database restore method.

Here’s the rough process for the five database restore:

  1. Backup the SQL databases from the source environment.
  2. Restore the SQL databases to the target environment.
  3. Create a new Web Application if one does not exist already.  You need to provision the new PWA site with the same URL as the old one, so if your source URL was http://source/PWA then you need to provision this to a Web App using the PWA address.  You may use something like http://target/PWA or even http://target:81/PWA, but it helps to use a Web App that doesn’t already have a PWA site provisioned already as that may have already used the default PWA URL.
  4. Use STSADM (or whatever tool you’d like) to add the content database to your new Web App.
  5. Provision a new PWA site with the same URL and databases that you restored.
  6. Review the sites to ensure that all of the data has transferred over properly.

Next up….I walk you through the five database restore process.

Project Server 2010 Database Restore (Part I)

10 thoughts on “Project Server 2010 Database Restore (Part I)

  1. adam says:

    You can also use one single method to restore corrupt project server database that is third party project server recovery software. I used Stellar Phoenix Project Server Recovery Software to repair all five databases.

  2. Bruno says:

    Nice articles, have couple of questions about how to “sync” PROD/DEV/TEST environments

    1) Does it work if we want to refresh the DEV environment with PROD PWA, database and settings, if we have use the above to copy a Three tier Farm (Web/App/Db servers – prod) to a Two Tier Dev (web-app/db – dev ?

    2) if in our design we have different service accounts installed in DEV/TEST/PROD, configured in the SQL db as well as the Service account for the sharepoint farm and sharepoint admin. I believe copying dbs over only without adjusting the accounts will not work.

    1. 1) Numbers of WFE shouldn’t really matter too much. At the end of the day, you already have a DEV SharePoint farm, and you’re just adding the dbs and provisioning a new PWA instance.
      2) Honestly, I’m not 100% sure as I haven’t played with that specific scenario. That being said, I think it probably would work, as you’re really just provisioning a new PWA site with old PWA data. Worth a try.

  3. John says:

    My question is similar to Bruno’s
    We want to refresh our Dev environment with production data. The Dev environment is structurally the same as the production environment.

    I want to use the Four Database restore into Dev to accomplish the refresh without provisioning a new PWA instance.
    Web site provisioning (other than PWA) is not a factor for this round of testing so we do not want or need Project sites.

    Do you know of any complications that may arise from doing this type of refresh?

    1. Michael Davis says:

      John, how did you get your issues in Project 2010 resolve after performing a four database restore?
      I take it the issue you’re talking about is the the PDP pages being gone and not being able to access the PWA project sites. I’m curious how you resolve this without pulling over the 5th (content) database.

Leave a Reply to adam Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s