Project Server 2010 Database Restore (Part II)

My last post provided an overview of the available options to move PWA from one farm to another.  In this post, I will walk through in more detail how to use the five database restore method to migrate a PWA instance from one farm to another.

First off, a disclaimer….if you plan to use this in a production environment, stop right now and look at using the Central Admin backup/restore method instead.  In my opinion, that gives you a much better fidelity reproduction of your source environment.

If you’re only looking to move data to a virtual environment to provide a proof of concept or a demonstration environment….or if for some reason your other disaster recovery techniques have failed and all you have are the five databases to rebuild your environment, then continue reading.

You should also make sure to read Brian Smith’s blog post on caveats with this approach.  This may give you a heads up on potential issues you may face.  Once you’ve done that, feel free to proceed.

So the challenge I set before myself was to pull the five databases from the Contoso demo image and restore the full PWA instance to a target environment that I had built myself.  The target environment was a slightly higher patch level.

1) Backup your source databases.

Open SQL Server Management Studio and identify the five required databases (Archive, Draft, Published, Reporting and Content):

Right click on the source databases, and select Tasks > Backup. 

Save the files in a place you will be able to find them again.

Afterwards, you should have all of the databases in one location.

2) Restore the databases to the target environment.

Open SQL Server Management Studio in the target environment, right click on the Databases heading, and choose the option to restore the database.

Choose a new name for the restored database.  In this case, I enter the name “PWA_Contoso_Archive.”  Select the option to restore from a device, specifically the location where you have stored the source data.

3) Create a new Web Application.

In my target environment, I already have a PWA site created at http://demo/pwa.  Hence, I need to create a second Web App to host the restored PWA.  In this case, I will create a second Web Application at http://demo:81 and then I will put my new PWA at http://demo:81/pwa.

Note that creating the new Web App creates a new content database to contain the Web Application data.

As a best practice, you should always create a site collection at the root of the Web App.  If you don’t you’ll run into issues when saving from Office applications or publishing forms from InfoPath.

4) Attach the restored content database to the new Web App.

Follow the instructions here to restore the content database to the new Web App.  Kind of feels good to use STSADM again, I must admit.  Thankfully, this is the closest I’ll ever get to being a developer.

5) Provision a new PWA site.

When provisioning the site, ensure two things:

• You use the same database names as the ones you restored.
• You use the same URL as the original….i.e. if the original was http://source/pwa, you’re restoring to http://target:81/pwa.  Do not restore to something like http://target:81/pwa2.

6) Validate the new site.

At this point, you’ll want to take a tour of your new PWA site to ensure that all of the data transferred.

Navigate to the Project Center and click on a project to ensure the PDP page appears.

Next, click on the Server Settings and review the Project Sites.

Observe that all of the sites appear to be properly linked.

Finally, navigate to one of the project workspaces and click around to ensure everything works.

I noticed that adding a new issue or risk doesn’t seem to work immediately.  Clicking on the link to add a new item yields an error.

To fix this error, simply click on the InfoPath Form option on the list ribbon, and then republish the form.  The list will now work.

….and there you are, a restored PWA site using the five database backup/restore method.

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: http://technet.microsoft.com/en-us/library/dd207307.aspx. 

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.