The Right Way to Configure Administrative Backup

Ran into this again this week, and figured it was worth a blog post.  As any administrator will tell you, Project Server provides the option to create scheduled backups of projects, settings, resources, and custom fields.  This is a great feature, but the user interface often leads to some confusion.  The goal of this post is to alleviate some of that confusion.

Before we do that though, let’s first address where to actually find the Administrative Backup settings:

  • Project Online (Office 365) – It’s not available for end user access.  That makes it simple.
  • Project 2013 (On-Premises) – Now found in the Manage options for the Project Web App in Central Admin’s Project Server Service Application (go ahead and send this link to your SharePoint administrator because they’ve probably locked you out – and if they haven’t, they probably should have.)
  • Project 2007-2010 – Project Server Server Settings.

Here’s what it looks like in the pre-release version of Project 2013:


Here’s what I typically see this set for in many clients:


Note that unless you have extenuating circumstances, the items highlighted in red probably need to be revisited:

  1. Project Retention Policy usually should be set to something along the order of 10 or less versions.  Anything higher than that would probably need to be vetted by your SQL folks and combined with the development of some database management protocols.  Otherwise, your Archive database is likely to grow too large to be safely managed.  I’d also point out that it’s a rare organization that truly requires more than 5 versions of the project.
  2. Everything else should be set to Never.  Why?  Because Project Server doesn’t retain multiple versions of these settings.  Every time you save a new version of the settings, it overwrites the old ones.  This means that if you make a change to the environment, and don’t catch an error, the setting will be overwritten that night when the administrative backup is run again.

Hence, I always encourage my clients to configure these settings as follows:


While I am on the topic, a couple other common misconceptions around this screen are probably worth mentioning:

  • Project versions are archived daily.  This means that if the project is edited multiple times in a single day, only the latest version is archived at the end of the day.
  • Project versions only get archived if something has changed.  If the project hasn’t been edited on a given day, the new version won’t be archived.  Note that you may need to revisit these settings if you have some sort of customization running that automatically updates the project file on a daily or weekly basis.  These updates would trigger the archive process, and potentially could result in overwriting the last good version of the project.
  • When you trigger an Administrative Backup, the backup may not simply overwrite the existing elements.  For example, if I backup the resource pool, then add a couple of resources accidentally, the administrative restore won’t really do all that much.  The only thing the administrative restore will do is restore any resources I may have accidentally deleted.  The same applies for views.  (Fields on the other hand appear to be restored to a point in time, so any new fields get removed.)

As for how this should all be used?  My general recommendation is to set the project retention policy to 5-10 projects, and then manually trigger the backups for everything else prior to a major change event.  I also tend to use this in conjunction with the Playbooks tool, which helpfully documents all of the configurations and custom fields into an XML file that may then be opened into Excel.  I’d definitely recommend including both options in your typical release checklist.

The Right Way to Configure Administrative Backup

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:


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 II)

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)