#ProjectServer 2010 environment rollover steps #PowerShell #PS2010 #MSProject #SP2010

April 10, 2012 at 6:56 pm | Posted in Administration, Functionality, Migration, PowerShell | 2 Comments
Tags: , ,

This blog post will detail the PowerShell commands required to carry out an environment rollover using Windows PowerShell where possible. The steps below use the 5 database approach to rollover over the Production environment to the Test / Development environment.

For more details on the Project Server database restore please see Andrew’s blog posts found below:



As a prerequisite I would recommend taking full backups of the Test / Dev farm to enable the environment to be rolled back if required. Use your usual farm backup procedures.

Test / Dev environment Prep

Firstly connect to the Test / Dev Application server and launch the SharePoint 2010 Management shell. If you are not familiar with the management shell it can be found in the Microsoft SharePoint 2010 Products folder – Start | All Programs | Microsoft SharePoint 2010 Products | SharePoint 2010 Management Shell.

Type the following:

Remove-SPProjectWebInstance –Url <URL of Test / Dev PWA site that you want to refresh with the Production data and config>



Press Enter and the command will remove the existing Test / Dev instance


You can check in Central admin in the Project Server Service Application to check that the PWA site has been removed:


Using the SharePoint 2010 Management Shell detach the existing content database from the web application that hosted the PWA site collection that was removed in the previous step.

Type the following:

Dismount-SPContentDatabase “<Database name here>”



Press Enter:


Press Y to confirm and Press Enter.

Test / Dev environment SQL Prep

Take a backup of the 5 databases from the Production environment and copy these over to the Test / Dev SQL server and restore the databases. The databases required are:

  • Content database that contains the PWA site and Project Sites
  • ProjectServer_Archive
  • ProjectServer_Draft
  • ProjectServer_Published
  • ProjectServer_Reporting

Make a note of the database names used when these databases are restored as they will be required later.

Test / Dev environment configuration

Using the SharePoint Management Shell, attach the restored content database to the web application that will host the PWA site collection.

Type the following:

Mount-SPContentDatabase “<content database name restored in previous step>” -DatabaseServer “<Test / DEV SQL Server>” –WebApplication <web application URL>



Press Enter:


Now using the SharePoint 2010 Management Shell provision the PWA site collection in the web application where the database was just attached to and using the 4 Project Server databases that were restored previously. Please note, use the same PWA path name used in Production. For example if the instance is called /PWA in Production, use /PWA in the command below on the Test / Dev environment.

Type the following command:

New-SPProjectWebInstance -Url “<web application URL + PWA path>” -AdminAccount “<Project Server Administrator account> -PrimaryDbserver “<Test / Dev SQL Server>” -PublishedDbname “<ProjectServer Published database>” -ArchiveDbname “<ProjectServer Archive database>” -DraftDbname “<ProjectServer Draft database>” -ReportingDbserver “<Test / Dev Reporting SQL Server>” -ReportingDbname “<ProjectServer Reporting database>”



Press Enter:


You can check the provisioning status of the PWA site using PowerShell or in Central admin in the Project Server Service Application to check that the PWA site has been created:

Type the following:

Get-SPProjectWebInstance –URL <PWA URL> | Select ProvisioningStatus



Or in Central Admin:


Post Provisioning

The Project Sites will need to be relinked using the “Bulk Update Project Sites” functionality in Server Settings on the newly provisioned Test / Dev PWA site.

The Project Server timer jobs (Resource capacity, Daily Scheduled backup and Cube build – also update the SQL AS server / cube name) will need to have the scheduled times modified on the Test / Dev system so that the Timer Jobs are recreated / relinked in Central Admin on the Test / Dev environment.

The Excel services reports will need to be updated to use the ODC files from the Test / Dev environment as they will currently point to the Production ODC files. This is done by opening the Excel reports in Excel, changing the ODC file then saving the file back to the library.

Check for missing config after #ProjectServer 2007/2010 PWA instance merge #PS2010 #PS2007 #MSProject

February 20, 2012 at 10:22 pm | Posted in Administration, Configuration, Functionality, Migration, T-SQL | Comments Off on Check for missing config after #ProjectServer 2007/2010 PWA instance merge #PS2010 #PS2007 #MSProject
Tags: , , , , ,

I recently worked on a Project to migrate two Project Server 2003 instances into one Project Server 2010 instance. We achieved this by migrating both Project Server 2003 instances to two separate Project Server 2007 instances then merged the smaller of the two instances into the other instance in 2007. Luckily we only needed to migrate the configuration from 2003 in 2010. For this we took a playbooks export of the global configuration from the smaller instance and imported this into the other PWA instance using the merge option. On the whole this approach worked quite well, with only a few errors on importing a couple of custom fields and views. There were some items that we needed to migrate manually (Resource Pool, Enterprise Global etc.).

Now that we had the two environments merged into one 2007 PWA instance we wanted to confirm that we had all of the entities from both systems in the one PWA instance that we were going to take forward to Project Server 2010. To do this I used a simple SQL query that runs against the Published database. Please note, it is not officially supported to query the published database directly with T-SQL so please do so at your own risk. I have used the no lock hint to avoid any tables locks and this was not run on a live production environment. The query used is below:

–SOURCEDB_Published is the PWA instance where the export was taken from

–TARGETDB_Published is the PWA instance where the exported config was imported and merged

–custom fields

use SOURCEDB_Published


from dbo.MSP_CUSTOM_FIELDS CF with (nolock)

where CF.MD_Prop_Name not in (select CFT.MD_PROP_NAME from TARGETDB_Published.dbo.MSP_CUSTOM_FIELDS CFT with (nolock))

–lookup tables

use SOURCEDB_Published

select LT.LT_NAME

from dbo.MSP_LOOKUP_TABLES LT with (nolock)

where LT.LT_NAME not in (select LTT.LT_NAME from TARGETDB_Published.dbo.MSP_LOOKUP_TABLES LTT with (nolock))

–lookup table values (text values)

use SOURCEDB_Published

select LV.LT_VALUE_FULL, LT.LT_name AS [Lookup Table Name]

from dbo.MSP_LOOKUP_TABLE_VALUES LV with (nolock)

Inner Join dbo.MSP_LOOKUP_TABLES LT with (nolock)


where LV.LT_VALUE_TEXT not in

(select Cast(LVT.LT_VALUE_FULL as nvarchar(max))

from TARGETDB_Published.dbo.MSP_LOOKUP_TABLE_VALUES LVT with (nolock)

Inner Join TARGETDB_Published.dbo.MSP_LOOKUP_TABLES LTT with (nolock)



use SOURCEDB_Published

select C.CAL_NAME

from dbo.MSP_CALENDARS C with (nolock)

where C.CAL_NAME not in (Select CT.CAL_NAME from TARGETDB_Published.dbo.MSP_CALENDARS CT with (nolock) where CT.Cal_is_base_Cal = 1)

–views (PWA)

use SOURCEDB_Published


from dbo.MSP_WEB_VIEW_REPORTS V with (nolock)

where V.WVIEW_NAME not in (Select VT.WVIEW_NAME from TARGETDB_Published.dbo.MSP_WEB_VIEW_REPORTS VT with (nolock))

–security categories

use SOURCEDB_Published


from dbo.MSP_WEB_SECURITY_CATEGORIES CAT with (nolock)

where CAT.WSEC_CAT_NAME not in (Select CAT.WSEC_CAT_NAME from TARGETDB_Published.dbo.MSP_WEB_SECURITY_CATEGORIES CAT with (nolock))

–security groups

use SOURCEDB_Published


from dbo.MSP_WEB_SECURITY_GROUPS G with (nolock)

where G.WSEC_GRP_NAME not in (Select GT.WSEC_GRP_NAME from TARGETDB_Published.dbo.MSP_WEB_SECURITY_GROUPS GT with (nolock))

Hopefully that will help you validate certain entities in the PWA instance when merging two instances into one.

Blog at WordPress.com.
Entries and comments feeds.