Moving Project Server 2007 Databases (Including all WSS DBs) RRS feed

  • Question

  • Hello all,

    A client asked me how to move PS2007 and all it's WSS related databases (DBs) to another SQL Database (DB) Server (with a different name). They are not moving the application or the WFEs to another server, but are only moving all the DBs to another DB server (with a different name).

    I found this document on TechNet and thought it was very useful, but after re-reading it, I do not think it caters for DB Server Name change...Am I referring to the right TechNet document?

    I also found this article which I am not sure if it applies to my client's refers to creating a new farm this necessary even though they are not moving the app server?

    Thanks in advance for any suggestions or clarification.



    Wednesday, March 2, 2011 4:11 AM


All replies

  • HI SJ,

    If you only intend to move the PWA project data from one database server to another, then you can follow these steps:

    1. backup all your database(using SQL)

    • Draft database

    • Published database

    • Archive database

    • Reporting database

    2. restore it to the new database server

    3. go to the central administration ->shared server provider

    4. hover your mouse over the PWA instace name and click edit from the drop down

    5. edit the name of the database server and databases and click edit

    6. wait for few mins and refresh the status - you site should be provisioned successfully.

    the above steps will enable you to move your project data to the separate database server.

    If you want your web app and PWA content db moved to the new database server as well, then you have to:

    1. backup the content db of your PWA and restore it to the new database server

    2. Create a new web application using the new database server name

    3. provision a new PWA site using the new web application and new database server

    4. once the site provisioned successfully, edit the site and change the database name with the one you restored earlier

    5. this will bring back your project data, but you need to relink your project workspaces with this new site

    to relink your project workspaces:

    6. create a new web application (using port for eg. 999)

    1.    7.  Once the Web application is created on port 999 > Navigate back to central administration > application management > Under SharePoint Web Application Management Click on Content databases

    2.      8. Change the Web-Application to http://<webappname>:999/

    9. remove the content db of the web app

    10. add content db ( the one you restored from your PWA) to this web application

    11. download project server resource kit for project server 2007

    12. use:  ReLinkAllWSSSites.exe http://<webapp>:999 http://<webapp>:80/pwa

    now your project work spaces should also be accessible.


    Let me know if its helps you, and this is exactly what you were looking for.

    | Khurram Jamshed | Follow my blog about Enterprise Project Management Solution | |
    Wednesday, March 2, 2011 7:09 AM
  • Thank you Khurram.

    As per my query above, I want to move all DBs (Web App related and Project Server related). I have already seen your first option (PS DB only) in TechNet so I will ignore that.

    I am a bit confused with your second option though...I can only see reference to Web App and Content DBs and you ended your sentence with 'as well'...I am assuming you have already incorporated the first two steps of your first option?

    4. once the site provisioned successfully, edit the site and change the database name with the one you restored earlier  - this step I guess you are referring to backed up and restored Project Server Databases?

    3. provision a new PWA site using the new web application and new database server - If my assumption is right about second option incorporating first two steps from the first option then I should be able to provision the PWA with the restored the DB? Unless it is different in PS2007...

    Again thank you very much for your response and once I get the clarification to the above I will let our client know.

    Kind regards,


    Wednesday, March 2, 2011 10:36 PM
  • Has anyone had any issues using server alias in this type of situation? I was thinking of suggesting the use of SQL Client Network Utility/SQL Server Configuration Manager to set server alias...this would mean that the client doesn't have to go touch the application server...

    Thursday, March 3, 2011 10:46 AM
  • Hi SJ,

    yes incase of 2nd option, the first 2 points from 1st option need to be considered.

    this way you will able to move all your dbs including:

    • content db of Web app that host PWA
    • content db of PWA
    • and 4db set of PWA

    to the new database server, except central admin content db. BUT you have to take care about the changed URL in this case, because you have to create a new web app to host the new PWA site and you will end up with the different URL to access PWA - so you need to consider this if you opt for this option.

    apart from this, let me know if this option looks ok to you and fulfill your requirement.

    | Khurram Jamshed | Follow my blog about Enterprise Project Management Solution | |
    Thursday, March 3, 2011 11:15 AM
  • Thanks.

    Gosh what a messy task to move DB server for SharePoint and PServer!

    Anyway...has anyone tried using this method...



    Thursday, March 3, 2011 9:44 PM
  • One more thing...if i can't move the central admin content db then that means I cannot migrate the configuration db also...this leaves me with creating a new farm option as per the above link...
    Friday, March 4, 2011 2:35 AM
  • Re SQL Alias..

    My organisation now builds all environments using a SQL Alias which helps when you run into this scenario of needing to move the backend databases around without wanting to touch the front end at all.

    I take it you are suggesting moving all the databases to a new DB server, then setting a SQL Alias on the new server for the old server name and decommissioning the old server? That should work. But you would need to test it first.

    Let us know how you go,

    Alex Burton | Twitter
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    • Marked as answer by SJ_PPM Tuesday, March 8, 2011 1:33 AM
    Saturday, March 5, 2011 3:55 PM
  • Thank you Alex.

    I suggested that option to the client last week also and they do not want to use SQL Alias...I know that will work in their situation. They must have reason not to use it, which I will ask them next week...are there any issues with using SQL Alias that you are aware of? Any kind of admin overhead? 

    I have already given them the instructions from as an option for them to consider.



    Sunday, March 6, 2011 10:16 AM
  • I agree with Alex.  Using a SQL Alias should always be the default option.
     I am not aware of any reason not to use one.

    Andrew Lavinsky [MVP] Twitter: @alavinsky
    Monday, March 7, 2011 3:04 PM
  • Thanks Andrew. I agree with both of to get the client's buy-in...

    Will let you know how I go, but I will mark this post as answered.

    Thank you so much!


    Tuesday, March 8, 2011 1:33 AM