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

image

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

image

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

image

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

image

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.

image

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.

image

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.

image

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.

image

5) Provision a new PWA site.

When provisioning the site, ensure two things:

image

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.

image

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

image

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

image

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.

image

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.

image

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

Advertisements
Project Server 2010 Database Restore (Part II)

39 thoughts on “Project Server 2010 Database Restore (Part II)

  1. SvenL says:

    HI, last week we make a db restore of all 5 Project Server databases. Everything works fine, except one thing. You wrote “Observe that all of the sites appear to be properly linked.” but you didn’t wrote what we should do, if the sites are not properly linked :). In our Environment there are some projects, which doesn’t have a Site link, but the sites are browseable if you enter the url manually. I even tried to add the link via “edit SiteAdress”, but i only get an warning: “The website does not exist or is not configured for Project Server”. Did you have any advices?

    1. I’ve run into that with 2007. The first thing that I would try is to confirm the syntax of the URL you are entering. As I recall, it will throw that error if you have a trailing “/” on the URL – or maybe it was if you don’t have a trailing “/.” Check the ones that do work to confirm you’re using the correct URL.

  2. Chris K says:

    Thank you for posting this, it was very helpful and I could not find the information anywhere else. I was especially tripped up at the last part, with list and library forms throwing File Not Found errors after performing the five-database restore. Unfortunately I had a couple of problems with the InfoPath solution.
    First, Customize Form in InfoPath is not available for library forms, only list forms. Library forms such as DispForm.aspx and EditForm.aspx throw the same File Not Found error as the list forms. I was able to locate the stack trace in ULS where the exception is thrown, but I’m just not smart enough to fix the forms themselves in Designer. I’m also not quite sure what InfoPath does under the covers with list forms, but their appearance definitely changes after republishing.
    Second, to fix the entire project implementation, every list in every project site would need to be opened in InfoPath and republished. That could be quite onerous.
    As a work-around I added the internal URL of the old farm to the alterrnate access mapping collection of the new farm and that seems to have fixed everything. I’m not happy about hack fixes like this but I can’t see any other way to make things right. Thanks again!

  3. PWAGuy says:

    Hey,
    I have followed these steps..but when I tried to create provision new pwa on target, it gave an error saying “The Project Web App path cannot be empty. Please enter a Project Web App path.
    The Project Web App site specified cannot be created because the site collection with that name exists or is referenced in an existing project object. Enter another Project Web App path and try again “…because after I attached the backup content DB, there is a sitecollection pwa ….I tried to delete pwa entry from Managed Paths list but still no luck…

    Can you suggest me something please?

      1. Matt Ludtke says:

        P.S. – It may be clear since I’m replying to this specific topic but wanted to note that i was having the same problem and can confirm the above link did resolve my issues.

        Thanks

  4. zaeb13 says:

    Regards!!

    i have followed all steps, but continue with a problems in the project sites.The option “Project Site Provisioning Settings” doesn’t show site’s URL. I have a question: when do i restore the content database “original” ( http://demo/pwa) can i delete the content database “clone” ( http://demo:81/pwa)?? Thanks for your answer.

    1. Not sure I follow, but different site collections can be in different content db. So if you’ve created http://demo:81/pwa already, you need to keep the content db that the main site collection is stored within (i.e. http://demo:81/) You may then restore the original content db and provision PWA within that content db to get back up and running. You’d have to test out all of the implications, and it’s been a while since I’ve tried this, but that should work.

  5. Valer1us says:

    Very Big Thnx, Andrew! This post is very helpful for me and my company, i killed over 4 days and thousands megabytes of traffic for migrate my ProjApp instanse. With Best Wishes, Valer1us.

  6. Elli says:

    Hi, love your post! We used this method to create our test server and we’re having a strange issue – for some reason, the PWA site is convinced we want to use Claims Authentication and gives us Security Token errors when we try to log in to the PWA site. We have checked all our settings, created yet a new Web Application and new PWA site, but we still get a
    “An exception occurred when trying to issue security token: The requested service, ‘http://localhost:32843/SecurityTokenServiceApplication/securitytoken.svc/actas’ could not be activated. See the server’s diagnostic trace logs for more information..”

    Error anytime someone goes to the new PWA site. We are using classic windows authentication and it works just fine on our prod server. I’m at a loss, hoping for some help here.

    Thanks!

  7. Atul says:

    I am soon going to try this out. One concern though before I proceed further. I have multiple EPTs, phases, stages, and n no. of custom PDP’s in my source environment. Do all the entities, especially the PDP,s (they can be irritating to create sometimes) come up as well.

    Thanks!

  8. We recently restored our Test environment with our Production database and PWA seems to be working except that it seems that all the links to the project PDP’s are broken. Any suggestions on how to fix this issue?

  9. Dave says:

    The “Customize Form” method of “fixing” the form seems to do so by removing the “Links” custom field (which is of PwaLinkCustomField type). I can accomplish the same thing by simply hiding the field. Unfortunately this is a problem since I want to see this data.

    The “Bulk Update Project Sites” fixes *some* File Not Found errors, but ignores the embedded links in the PwaLinkCustomField, which causes Issues etc. to break the view/edit detail form when issues containing Links are opened. The field is read-only XML in datasheet view, so can’t be edited, similar to a Managed Metadata field. It is apparently a serialized PwsLinkDataSet, which retains the original URL for linked items.

    http://technet.microsoft.com/en-us/library/ms424894(v=office.12).aspx

    sample Linked Item XML after 5db restore follows. Apparently the invalid ListID or the old-server URL causes the view/edit forms to break:

    db972df4-c4fd-4a15-969c-85985a4c71c9
    2
    00000000-0000-0000-0000-000000000000
    Task Title
    1
    1
    429b9e2d-427e-4d70-9a4c-06099981648c
    2
    http://old-server/pwa/projectdrilldown.aspx?_projectUID=11ac16e8-a1c8-470a-8102-cdd3ff2a3556&_taskUID=db972df4-c4fd-4a15-969c-85985a4c71c9

    If anyone has any suggestions on how to recover the “Project Links” field for Issues, etc. after a 5 db restore I would love to hear them. All I can think of is directly editing them in the Content database.

    thanks.

  10. Dave says:

    Sorry about that missing XML code, didn’t know it would be stripped out. Turns out the problem was the old-server URL. It can be fixed by re-publishing the project after running the Bulk Update to relink. Finally got it working with Links intact!

  11. Brad Waller says:

    I am having a problem with this.
    When I go to create the new PWA site using the same url as the old one (http://project.domain.com) I get an error saying there is already a url using that.

    The only thing I can find is that there is a site colletion in my content database called project.domain.com and has the url http://project.domain.com

    Any idea’s on what i should try?

    I have attempted the Sharepoint restore, but it error’s on all the databases it tries to restore.

  12. Bob O'Reilly says:

    Hi,

    I tried this and all is working correctly expect the Projects Sites are still linked to the Old Sites and have not changed address as shown. I am wokring on the same test server, could this have caused the issue, or have i missed a step along the way? Thanks again!

  13. NIKRAJ says:

    this discussion is so helpfull. I need, an example of Archive and Publish Data base, to do some reporting test on Project Server 2010, thank you.

  14. subachids says:

    i want to connect project server database from local machine with sharepoint instance , am not able to connect using sql authentication ,pl tell me wat i ned to do

  15. Nee says:

    Thank you for this documentation. It helped upgrade to Project Server 2010. However, I am missing some settings under Operational Policies. I need help figuring out how I can get them back:
    I am missing thes options:
    1. Project Sites
    2. Project Site Provisioning Settings
    3. Bulk Update Project Sites

    I thank you in advance
    Nee

  16. megha says:

    Hi,
    I have a scenario where I need to restore PWA which is created on root site by giving a Project Web App Path as “pwa.abc.com” and selecting “Use Project Web App path as host header”.

    Is it possible to restore such a web application using the 5DB method. Please suggest

    Regards,
    Megha

  17. Sai says:

    Andrew,

    Thanks for the article. I attended your session in SharePoint Saturday in Houston. It was great.
    Currently, I am trying to take project server 2010 databases from PROD and refresh them in the DEV environment by using 5 database restore process.

    When I tried initially, the PWA site was missing the Project Site urls under Server Settings -> Project Sites page. Then, I deleted the SharePoint web application and also deleted the Project Server related 5 DBs manually. I restored the 5 DBs and added the project server content db to the newly created web application. When I tried to Provision the Project Web Application (PWA), it failed and I observed the following error message in Event Log:

    Log Entry 1:
    Failed to provision site PWA with error: System.NullReferenceException: Object reference not set to an instance of an object.
    at Microsoft.SharePoint.SPSite..ctor(Guid id, SPFarm farm, SPUrlZone zone, SPUserToken userToken)
    at Microsoft.SharePoint.SPSite..ctor(Guid id)
    at Microsoft.Office.Project.Server.Administration.ProjectSite..ctor(Guid webAppId, Guid siteId, String adminName, Int32 lcid, Boolean needsUpgrade, PsiServiceApplication parent)
    at Microsoft.Office.Project.Server.Administration.ProjectSiteCollection.Add(Guid webAppId, Guid siteId, String adminName, Int32 lcid, String PubDBConnString, String VerDBConnString, String WorkDBConnString, String RepDBConnString, Boolean needsUpgrade)
    at Microsoft.Office.Project.Server.Administration.PsiServiceApplication.CreateSite(ProjectProvisionSettings provset)

    Log Entry 2: Provisioning ‘PWA’: Project site object creation failed. Exception ‘System.NullReferenceException: Object reference not set to an instance of an object.
    at Microsoft.SharePoint.SPSite..ctor(Guid id, SPFarm farm, SPUrlZone zone, SPUserToken userToken)
    at Microsoft.SharePoint.SPSite..ctor(Guid id)
    at Microsoft.Office.Project.Server.Administration.ProjectSite..ctor(Guid webAppId, Guid siteId, String adminName, Int32 lcid, Boolean needsUpgrade, PsiServiceApplication parent)
    at Microsoft.Office.Project.Server.Administration.ProjectSiteCollection.Add(Guid webAppId, Guid siteId, String adminName, Int32 lcid, String PubDBConnString, String VerDBConnString, String WorkDBConnString, String RepDBConnString, Boolean needsUpgrade)
    at Microsoft.Office.Project.Server.Administration.PsiServiceApplication.CreateSite(ProjectProvisionSettings provset)’.

    Log Entry 3: ProjectSite not created.

    Do you have any thoughts ?

    If I create Project Web Application with different name other than PWA, it worked fine but there are other issues as Project Details Pages are missing, and so on

    Thanks,
    Sai KRishna Rachakonda

      1. Sai says:

        Thanks for the reply.
        Instead of deleting the PWA application from Project Service Application, I accidentally deleted the SharePoint web application first. When I deleted the SP web application which holds the PWA site collection, it left some config in the SharePoint config database. This is blocking me to recreate PWA application with same name.

        I checked other blogs to delete orphaned PWA application and able to delete the orphaned PWA application and timer jobs but still looks like some config is left out in the config database.

        Do you have any thoughts?

  18. Finally, I am able to resolve the issues which I mentioned above.

    Something in the SharePoint config or Project Server Config was corrupted.

    After hours of trial, I ran the Project Server Installation wizard and did Repair of the installation. The repair process went successfully. After that, I did following:
    a. Created Web Application with default root site collection.
    b. Ensure that site is up and working fine.
    c. Go to manage content databases, remove the content database for the above created web application.
    d. Restored all the 5 databases in the database server.
    e. Attached the project server content database(s) to the web application using the STSADM command (not thru UI). After attaching, if we go to View Site Collections option from Central Admin, we should be able to see PWA site collection for the above created web application.
    f. Navigated to Project Server Service Application and created a new Project Web Application by pointing to restored Archive, Published, Reporting, Draft databases.
    g. Project Web Application provisioning took sometime and provisioned successfully.
    h. When I opened the PWA site, the site came up fine but faced few minor issues which I resolved with the help of following posts:
    http://rsaikrishna.blogspot.com/2013/05/project-server-2010-pwa-errors-and.html
    http://rsaikrishna.blogspot.com/2013/04/project-server-2010-common-errors-when.html

    Finally happy with output 🙂

  19. Kong says:

    Hi,

    I have tried around 4 times but I am unable to Provision a new PWA site at step 5.
    I got an error: Failed – see the Application event Log.

    I have checked and seen (at http://sp2010prodcaost:1024/_admin/CNTDBADM.aspx) that when I attached content DB to WebApplication. It have contained 1 site collection already, it is correct?

    Please advices,

    Many thanks,
    Kong

  20. ikahn says:

    I restored the DB’s as per the steps mentioned above but now I cannot view the Project Schedule with my log-in. When I log-in with service account, then I can view the Project Schedule. Please help!

    1. This might caused by security. Have you check your log in account have permission to view Project schedules? or your login account is in PM group or category group,..

  21. KenB says:

    Thanks for this detailed post. All went well except the parent PWA site data was overwritten within the content db. The sub-site data still exists, but is orphaned due to the new parent site guid. Was the content DB supposed to be in a read-only state?

    Prod > Dev Same SQL Server Different instance. Same patch level. One difference was Dynamic WF feature is not installed in dev farm.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s