PowerShell Script to automate the data refresh for Project Server 2010
Repeatable Data Refresh Project Server 2010
Scenario: You need one or more additional Project Server environments for your patching, testing and development processes. You want an easy and repeatable way to refresh an environment to match the latest production data and EPM configuration. Project Server may or may not be part of your organizations main SharePoint farm, storage is not an issue and you do not need the Reporting and BI service application components restored each time, just the Project Server databases and associated SharePoint content databases.
Audience
SharePoint farm administrators, Project Server farm administrators, and disaster recovery engineers. This process can be used as part of your Disaster Recovery plans in order to develop a comprehensive procedure.
Environment
This procedure is for SharePoint 2010 and Project Server 2010 to refresh databases on different servers in the same AD domain. This should work on 2013 versions but has not been tested.
Resources
These links refer to official Microsoft documentation, however, when in doubt, please refer to TechNet articles. You will also find detailed PowerShell commands and detailed step by step instructions that will help you perform the process described in this document.
Copy configuration settings between farms (SharePoint Server 2010)
Best Practices for Backup and Restore
Project Server Database Only Restore
The following is a repeatable way to build or refresh a Development (or other) environment to match the latest production data and EPM configuration. This scenario will use an existing farm (target farm) where PWA already exists. Your target servers will have different names and possibly different accounts but they must be part of the same domain as production for the procedures to work.
Prepare your target Servers
The following is a check list to complete before beginning the physical restore process.
Windows Server version and patch level should match production servers and SharePoint version and patch level must match production. The SQL version, collation, and patching levels should match between Production and Target environments.
Restore a copy of the SQL databases from Production SQL instance to the target SQL instance, using an SQL tool of your choice. (It is often simpler to take the previous night’s backups for restoration) This should include 5 databases as a minimum, all 4 Project server databases and the project sites content database(s). Note: this procedure does not include automating this step in a repeatable way, typically the DBA will handle it.
Make sure your Farm Admin account is in Local Admin Security group on the WFE box and that it belongs to the correct SQL Roles (Security Admin and DB Creator). Also, assign dbo rights for the new Farm Admin account to each database after a copy of the databases are in place on the target SQL environment.
Since the target PWA already exists, make sure it is running and tested before you start the procedure so you know the host headers, IP numbers etc. are all in place and operational in case of errors during the procedure.
The target web application is built matching production Authentication providers such as NTLM or Claims etc.
Running the Procedure Manually
The following is a checklist of steps to take on one of the Web Front End (WFE) servers to complete the restoration process manually. The script below automates this in one command.
Delete the old PWA Site and Site Collection. From Central Admin navigate to Service Applications > Choose appropriate “Project Server Service App” > “Manage Project Web App Sites” page, click Delete.
Detach the Project Sites Content DB from the Web App that Project Server is using. From Central Admin navigate to Application Management > Manage Content Databases > select your Web App and click the Database name in question and check the box “Remove Content Database”.
Re-start IIS (optional).
Mount the Project Sites Content Database. From Central Admin go back to “Manage Content Databases”, select your Web App and add your database.
Provision new PWA Instance from “Manage Project Web App Sites” page in Central Admin. (Make sure to enter the 4 Project Server database names that match the restored names).
Test the PWA instance.
Re-deploy or install any custom solutions, third party web parts as required.
Final testing.
PowerShell Script to Automate the Refresh
#Begin Script
# Use this script to refresh Project Server with new data from another Farm.
# This script is NOT meant to run in production. There is no error handling, although you can make your own or use the built in error handling.
#Instructions
# First, have your SQL databases restored to the target database server (reference that server with the $DataBaseServer parameter below).
# Change the parameters section to suit your environment.
# Run this script in PowerShell ISE or the Windows PowerShell command line.
##### Start Parameters section
$FarmAdmin = "rlan\spadmin" #Make sure the farm admin account you use has dbo rights on the 5 databases
$DBPrefix = "SP5-2" #I use this to prefix the database names, helps organize databases on SQL box
$Protocol = "https://"
$MyHostHeader = "ps.rlan.ca" #Replace the host header with your own value.
$PwaName = "PWA"
$WebAppPort = 80
$DataBaseServer = "sql1" #Replace with your own DB name or sqlalias.
$Archive = "ProjectServer_Archive"
$Draft = "ProjectServer_Draft"
$Published = "ProjectServer_Published"
$reporting = "ProjectServer_Reporting"
$PWAContent = "WSS_Content"
###### end paramaters section,
### Do NOT change anything below this line. ###
$ver = $host | select version
if ($ver.Version.Major -gt 1) {$Host.Runspace.ThreadOptions = "ReuseThread"}
Add-PsSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
$ProjectWebAppURL = "$Protocol$MyHostHeader"
$PwaWeb = "$Protocol$MyHostHeader/$PwaName"
# Remove PWA and dismount the content db (sites)
# Cannot dismount first, will get error site is missing
Remove-SPProjectWebInstance -Url $PwaWeb
Sleep 5
Dismount-SPContentDatabase $PWAContent
# You may get this error New-SPProjectWebInstance : Database validation failed. Reason: DatabaseInUse.
# so this may need to sleep longer, test in your environment, and run it again
sleep 5
# Re-provision with new databases Create Project Service Application
Mount-SPContentDatabase $PWAContent -DatabaseServer $DataBaseServer -WebApplication $ProjectWebAppURL
sleep 5
New-SPProjectWebInstance -AdminAccount $FarmAdmin -Url ($ProjectWebAppURL+ "/" + $PwaName) -ReportingDbserver $DataBaseServer -ArchiveDbname ($DBPrefix+$PwaName +"_Archive") -DraftDbname ($DBPrefix+$PwaName +"_Draft") -PrimaryDbserver $DataBaseServer -PublishedDbname ($DBPrefix+$PwaName +"_Published") -ReportingDbname ($DBPrefix+$PwaName +"_Reporting") -Lcid 1033
#optionally look at properties
#$SPProjectWebInstance = Get-SPServiceApplication | ? {$_.TypeName -like "*Project*"} | Get-SPProjectWebInstance
#optionally remove re-provision SA
#Get-SPServiceApplication –Name $saName | remove-spserviceapplication
#New-SPProjectServiceApplication –Name $SvcAppName –ApplicationPool $ProjectWebAppPool –Proxy
#End Script
How to run the script automatically on a pre-determined schedule
https://get-spscripts.com/2011/01/running-sharepoint-powershell-script.html
How to edit and execute the script in PowerShell ISE (recommended)