Migrating Project Server 2013 database between environments
By blurg64
A common requirement when setting up a new Project Server instance, is to replicate production data back into another environment such as Development or Test. In doing so, you have an environment where you can develop, test configuration changes or even perform training against, without impacting your production Project Server instance.
In this post we will walkthrough migrating data from our production Project Server 2013 environment which will be the source, over to a development / test environment which will be the target using the simple and fool-proof database attach method.
As with all these posts there are a few assumptions:
- All of your environments are at the same patch level for SharePoint and Project Server
- The target environment has been ‘cleaned’, removing any existing PWA and SharePoint content and associated databases prior to restoration.
- You are on the same domain
So let’s get started…
Backup your source databases
On the source environment, backup the SharePoint content database hosting PWA and site collections and the Project Service Database (as this is most likely from a production environment you could always just take a copy of your production backups that you have correct? 😉 ).
Restore to your target environment
In your target environment, restore the two databases into SQL, the Project Service Database and the Content DB that contains the PWA site and Project Workspaces you wish to migrate.
Once restored, check that each of the databases has the correct permissions assigned (db_owner). In my example here, I am using a single account demo box (demo\administrator), but you will need to use the correct account for your environment.
Test and Mount the SharePoint Content Database
Before mounting the SharePoint Content database, it is a wise move to Test the Content database against the web app you intend to restore it to. In performing the test, SharePoint will check the contents of the Content DB against the target web application to see if all the features, customisations etc. are present. If they are not, this step will highlight what is missing so you can rectify the problems.
To Test the SharePoint Content DB, enter the following command in a SharePoint Management Shell running as an administrator.
[sourcecode]
Test-SPContentDatabase –Name
[/sourcecode]
In this case you can see there is a missing setup file and web part that I should correct before proceeding, but as the test output shows, they are not Upgrade blocking problems, so I can continue if I wish.
Once the test is complete, the next step is to mount the database using this command:
[sourcecode]
Mount-SPContentDB –Name
[/sourcecode]
Again, just like the upgrade you need to make sure you have access to the PWA site collection, to do so, either enter the following command:
[sourcecode]
Set-SPSite –Identity
[/sourcecode]
Mount and Test Project Service Database
Next we need to Mount then Test the Project Service Database. This step is slightly different than with the SharePoint Content databases in that you need to mount the database before testing it, however SharePoint will let you test a content database whilst the database is not mounted.
[sourcecode]
Mount-SPProjectDatabase –Name
[/sourcecode]
Once mounted, enter the following command to test the Project Service Database:
[sourcecode]
Test-SPProjectDatabase –Name
[/sourcecode]
Here you can see we have a clean bill of health and are fine to proceed to the next stage and wire up the Project Web Instance.
Mount and Test Project Web Instance
Finally, now that the Content database and Project Service database have been mounted into SharePoint, all that is needed is to wire them up together by using the Mount-SPProjectWebInstance command:
[sourcecode]
Mount-SPProjectWebInstance –DatabaseName
[/sourcecode]
Once mounted, run the test command to check that all is wired up correctly:
[sourcecode]
Test-SPProjectWebInstance
[/sourcecode]
From my testing, this will return that one of the tests, the check for the Queue status will return a FailedWarning initially as the queue is starting up, then a few minutes later it will show that the status is Passed.
There should be no need to enable any features as we are moving from one 2013 environment to another.
Post Migration Tasks
If you development / test environment is running under a different administrator account, then make sure you navigate to Central Administration > Manage Service Apps > Project Server Service App and choose to Edit your Project Server Instance, modifying the Administrator account to be the correct account for your environment.
Next, choose to Manage the PWA instance and use the Bulk Update Connected Project Sites to ensure the workspaces are relinked and reassociated with the correct development / test instance and not still linked to Production. Finally if you have Reports or OLAP cubes, you will need to reconfigure those to work with the restored databases.
And that’s it, all that is outstanding is to test your migration, checking you have access to all the capabilities and data before releasing the environment back to your dev’s or testers.
Oh and one last thing, I would highly recommend once you have migrated your environments that you do something to differentiate the environment from production, either by changing the look and feel / colour scheme, or by editing the main PWA page to but a ‘Welcome to the DEV site’. You only have to do something in the wrong environment once to realise what a good idea this is 