Export user data from Project Server

Important!: This article describes how to export user data from Project Server 2016, Project Server 2013, or Project Server 2010. The process to export user data from Project Server 2019 is very different from the previous version, and is not contained in this article. To learn how to export user data from previous versions of Project Server 2019 Public Preview, see Export user data in Project Server 2019 Public Preview.

Your organization can export a specific user's content from your Project Server environment. To export this content, a Project Server farm administrator can follow these steps:

Step 1 - Download the export script files

Step 2- Find the Project Web App instances in your SharePoint Server farm

Step 3- Export workspace items for the user

Step 4 - Find the user's Resource ID or Claims Account on each PWA site

Step 5 - Find projects that contain the user you're looking for

Step 6 - Additional queries to export data

Step 7 - Archived items

Step 8 - Find and save attachments, views, and VBA files

Process Overview

The following is an overview of the process to export a specific user's information from Project Web App:

  1. Download the export scripts: Download the .sql and Microsoft PowerShell scripts for exporting user data.

  2. Find the PWA sites in your environment: Find a listing of Project Web App instances in your Project Server farm.

  3. Find the user's resource ID: On each Project Web App instance, find the unique Resource ID for the user. You can also choose to specify the user claim.

  4. Perform an export of the user's data: Export the information that you want to review by using the scipts.

    Using scripts for different versions of Project Server

This article applies to Project Server 2016, Project Server 2013 and Project Server 2010. While the general process applies to all three versions, there are specifics that may apply to the different versions, especially when running the SQL scripts. These are noted in the sections below. Be sure you have deployed the latest updates to your farm and Project Professional clients.

Note

Project Author is not exported as part of the procedures in this article.

Step 1 - Download the export script files

Download the export scripts from the Microsoft Download Center.

The download contains a ZIP file with separate folders for each version of Project Server. Use the scripts for your version or Project Server as described in Step 5, below.

Important notes about running the export scripts:

  • The script folder contains several .wsdl files. These are required by the PowerShell scripts. Be sure they are in the same directory as the PowerShell scripts when you run them.

  • The SetupReportingProcedures201x.sql script temporarily creates some global stored procedures in memory that are available within the sql session. These stored procedures are required by the following scripts:

    • ExportReportingProject201x.sql

    • ExportReportingResource201x.sql

    • ExportTimesheetReporting201x.sql

Note

Run SetupReportingProcedures201x.sql before running any of these scripts.

  • Each script has one or more variables that must be defined - such as UserID or database name - before you run it. Check the description section in the script itself for any needed parameters.

  • Run each .sql script in the context of the database where the information resides. You must have db_datareader permissions on the database.

  • You may need to "unblock" the zip file because by default, executing scripts downloaded from the Internet is not allowed. Do the following to unblock your files:

  1. In File Explorer, go to the location where you saved the zip file.

  2. Right click on the zip file, and click Properties.

  3. On the General tab, select Unblock. Unblocking the file.

  4. Click OK.

    All files contained in the zip file should now be Unblocked. You can verify this in the individual files by checking to see if the Unblocked checkbox option no longer appears in the General tab of the file's Properties page.

    Note

    If you only have access to unzipped files, you can also unblock each file individually.

Step 2- Find the Project Web App instances in your SharePoint Server farm

Use the Get-SPProjectWebInstance cmdlet with the following filters to get the URL, site ID, and database name for the PWA sites that exist in the SharePoint Server farm:

 Get-SPProjectWebInstance | ft -a Url,SiteId,DatabaseName,DatabaseServer

You will need the information for each site when you delete the user's personal data in a later step.

For example, running the cmdlet on our sample Contoso Project Server farm might return the following three PWA sites:

URL SiteID Database DatabaseServer
https://contoso/pwa1
63ed0197-3647-4279-ed5e80855fc7
WSS_Content
SQL01
https://contoso/pwa2
67fd0727-5279-3321-ef4e90956fc8
WSS_Content
SQL01
https://contoso/pwa3
63ed0197-3647-4279-eg7e20233fg9
WSS_Content
SQL02

Find the Project Web App instances in a SharePoint Server 2010 farm

For Project Server 2010, you also need to find the Service Application ID of the Project Server PSI Service Application . Run the Get-ServiceApplication PowerShell cmdlet with the following parameters to do this:

Get-SPServiceApplication | ? { $_.TypeName -eq "Project Server PSI Service Application" } | ft -a

This will also return the name of the Project Server service application. You can then use the Get-SPProjectWebInstance cmdlet to return the names of the four Project Server databases by specifying the service application name with the -ServiceApplication parameter:

Get-SPProjectWebInstance -ServiceApplication "Project Server"  | ft -a Url,PrimaryServer,PublishedDatabase,DraftDatabase,ArchiveDatabase,ReportingServer,ReportingDatabase

You need to be able to reference the database names for each database.

Note

The Project Server 2010 Reporting database can be located on a different instance of SQL Server than the other three databases.

Step 3 - Export workspace items for the user

Run the ExportWorkspaceItemsByDisplayName201x.sql script and search for data using possible display names of the user (partial name searches).

Run the script on the Reporting database for Project Server 2010, or on the database for the related PWA site for later versions. In the example results provided in Step 1, the database for all three Project Web App instances is WSS_Content .

Provide values for the following parameters in the script:

Parameter Description
@siteID (Project Server 2016 only)
The PWA site ID for the site in which you want to find the user's Resource ID. You found the PWA site ID values for your PWA sites in Step 1.
@resDisplayName
The display name, or partial display name, of the Project Server user.

Step 4 - Find the user's Resource ID or Claims Account on each PWA site

After getting information all PWA sites on your Project Server farm, next you need to find the Resource ID (ResID) or Claims account of the user whose personal data you want to delete. Do this on each of the PWA sites your discovered in Step 1 (since ResIDs differ in each PWA instance).

Run the FindUser201x.sql SQL script to find the user's Resource ID or claims account.

Note

You need to run the FindUser201x.sql SQL script in SQL Server Management Studio and must have farm admin permissions to have access to the appropriate database.

Run the script on the Published database for Project Server 2010, or on the database for the related PWA site for later versions. In the example results provided in Step 1, the database for all three Project Web App instances is WSS_Content .

Provide values for the following parameters in the script:

Parameter Description
@siteID (Project Server 2016 only)
The PWA site ID for the site in which you want to find the user's Resource ID. You found the PWA site ID values for your PWA sites in Step 1.
@searchName
The display name of the Project Server user.

For example, if you want to find the userID for Adam Barr on the Contoso PWA1 site you found in the example in Step 1, you would edit the values for the parameters in the script like this:

DECLARE @siteId uniqueidentifier = '63ed0197-3647-4279-ed5e80855fc7'
DECLARE @searchName nvarchar(255) = 'Adam Barr'

The script returns the Resource Name, Resource ID, email address, and Claims Account values for the user.

Step 5 - Find projects that contain the user you're looking for

You can use the Resource ID that you found in Step 4 to locate the projects that the user was involved with. This is done by using SQL scripts to query the Project data stored for a list of projects.

There are separate scripts for each of the data stores in Project Server. Results from the scripts are likely to be similar, though you may see some differences if you have draft projects that haven't been published.

Before you run the scripts, update them with the Resource ID that you're looking for.

For Project Server 2010, run these scripts:

(Be sure to read the description at the top of each script. Some scripts require you to add the database name or update other parameters.)

  • ExportDraftProjectList2010.sql

  • ExportPublishedProjectList2010.sql

  • ExportReportingProjectList2010.sql

For Project Server 2013, run these scripts:

  • ExportDraftProjectList2013.sql

  • ExportPublishedProjectList2013.sql

  • ExportReportingProjectList2013.sql

For Project Server 2016, run these scripts:

  • ExportDraftProjectList2016.sql

  • ExportPublishedProjectList2016.sql

  • ExportReportingProjectList2016.sql

Examine the output of the queries and determine the projects where you want to find specific user data. You may want to export the list from SQL Server to a CSV file for convenience.

When you have determined which projects you want to search for user information, run the following scripts for each project, using the ProjectUID returned by the scripts above:

For Project Server 2010, run these scripts:

(Be sure to read the description at the top of each script. Some scripts require you to add the database name or update other parameters.)

  • ExportDraftProject2010.sql

  • ExportPublishedProject2010.sql

  • ExportReportingProjects2010.sql

  • ExportReportingProjectTimephasedData2010.sql

For Project Server 2013, run these scripts:

  • ExportDraftProject2013.sql

  • ExportPublishedProject2013.sql

  • ExportReportingProjects2013.sql

  • ExportReportingProjectTimephasedData2013.sql

For Project Server 2016, run these scripts:

  • ExportDraftProject2016.sql

  • ExportPublishedProject2016.sql

  • ExportReportingProjects2016.sql

  • ExportReportingProjectsTimephased2016.sql

For information about the output values of these queries, see Project-specific user data from the reporting data.

If you need additional user information, see Step 6 for scripts to retrieve information about resources, timesheets, statusing, etc.

Step 6 - Additional queries to export data

Run these additional queries to find additional information about resources, timesheets, statusing, etc.

See Running the PowerShell scripts below for information about how to run the PowerShell scripts.

Export data from Project Server 2010

To export data from Project Server 2010, use the .sql scripts and Microsoft PowerShell scripts as noted in the following table. For details about each of the fields in the output, see the link in the Output definitions column.

(Be sure to read the description at the top of each script. Some scripts require you to add the database name or update other parameters.)

Export option Run these scripts: Output definitions
Portfolio
ExportPortfolioModels2010.sql
Drivers
Prioritizations
Analyses
Resource plans
Export-ResourcePlanTimephasedData2010.ps1
ExportResourcePlans2010.sql
ExportReportingResourcePlans2010.sql
ResourcePlan
Resources
ExportResource2010.sql
ExportReportingResource2010.sql
Resources
ReportingResource
Security
ExportSecurity2010.sql
Security
Service Settings
ExportServerSettings2010.sql
QueueJobs
CustomFields
LookupTables
Calendars
UnsubscribedAlerts
SubscribedReminders
ReminderEmails
Delegations
Status reports
ExportStatusReports2010.sql
StatusReports
TaskStatus
ExportAssignmentsSavedData2010.sql
ExportSubmittedTaskStatusUpdates2010.sql
ExportAssignmentTransactionHistory2010.sql
ExportAssignmentHistoryData2010.ps1
ExportSavedTaskStatusUpdates2010.sql
Export-SavedTaskStatusUpdates2010.ps1 (Note)
StatusAssignSaved
StatusAssignHistory
Timesheets
ExportTimesheets2010.sql
ExportReportingTimesheets2010.sql
Timesheets
Timesheets_Reporting
User view settings
Export-UserViewSettings2010.ps1
UserViewSettings
Workflow
ExportWorkflow2010.sql
Workflow
Workspace items
ExportWorkspaceItemsByDisplayName2010.sql
WorkspaceItems

Export data from Project Server 2013

To export data from Project Server 2013, use the .sql scripts and Microsoft PowerShell scripts as noted in the following table. For details about each of the fields in the output, see the link in the Output definitions column.

Export option Run these scripts: Output definitions
Portfolio
ExportPortfolioModels2013.sql
Drivers
Prioritizations
Analyses
Resource plans
ExportResourcePlanTimephasedData2013.ps1
ExportResourcePlans2013.sql
ResourcePlan
Resources
ExportResource2013.sql
ExportReportingResource2013.sql
Resource
ReportingResource
Security
ExportSecurity2013.sql
Security
Service Settings
ExportServerSettings2013.sql
QueueJobs
CustomFields
LookupTables
Calendars
UnsubscribedAlerts
SubscribedReminders
ReminderEmails
Delegations
Status reports
ExportStatusReports2013.sql
StatusReports
TaskStatus
ExportAssignmentsSavedData2013.sql
ExportSubmittedTaskStatusUpdates2013.sql
ExportAssignmentTransactionHistory2013.sql
ExportAssignmentHistoryData2013.ps1
ExportSavedTaskStatusUpdates2013.sql
Export-SavedTaskStatusUpdates2013.ps1 (Note)
StatusAssignSaved
StatusAssignHistory
Timesheets
ExportTimesheets2013.sql
ExportReportingTimesheets2013.sql
Timesheets
Timesheets_Reporting
User view settings
Export-UserViewSettings2013.ps1
UserViewSettings
Workflow
ExportWorkflow2013.sql
Workflow
Workspace items
ExportWorkspaceItemsByDisplayName2013.sql
WorkspaceItems

Export data from Project Server 2016

To export data from Project Server 2016, use the .sql scripts and Microsoft PowerShell scripts as noted in the following table. For details about each of the fields in the output, see the link in the Output definitions column.

Export option Run these scripts: Output definitions
Engagements
ExportEngagementScripts2016.sql
Engagements
Portfolio
ExportPortfolioModels2016.sql
Drivers
Prioritizations
Analyses
Resource plans
ExportResourcePlans2016.sql
ResourcePlan
Resources
ExportResource2016.sql
ExportReportingResource.sql
Resource
ReportingResource
Security
ExportSecurity2016.sql
Security
Service Settings
ExportServerSettings2016.sql
QueueJobs
CustomFields
LookupTables
Calendars
UnsubscribedAlerts
SubscribedReminders
ReminderEmails
Delegations
Status reports
ExportStatusReports2016.sql
StatusReports
TaskStatus
ExportAssignmentsSavedData2016.sql
ExportSubmittedTaskStatusUpdates2016.sql
ExportAssignmentTransactionHistory2016.sql
ExportAssignmentHistoryData.ps1
ExportSavedTaskStatusUpdates2016.sql
Export-SavedTaskStatusUpdates2016.ps1
StatusAssignSaved
StatusAssignHistory
Timesheets
ExportTimesheets2016.sql
ExportReportingTimesheets2016.sql
Timesheets
Timesheets_Reporting
User view settings
Export-UserViewSettings2016.ps1
UserViewSettings
Workflow
ExportWorkflow2016.sql
Workflow
Workspace items
ExportWorkspaceItemsByDisplayName2016.sql
WorkspaceItems

Step 7 - Archived items

ExportArchievdData201x.sql will return the following data that is stored in the archived database that is related to the resource.

Export option Output definitions
Archived items - Calendar Calendars
Archived items - Custom fields CustomFields
Archived items - Lookup tables Lookup Table
Archived items - Projects Project List
ProjectVersionId (Archive version ID)
ProjectVersionDescription (Date and time of the backup)
ProjectVersionDate (The date of the backup)
Archived items - Resource Resource
Archived items - Resource custom fields Resource - custom fields

Archived Project Data: To export archived projects:

  1. Archive the current project. (2010)
  2. Restore the archived version. (2010)
  3. Export the user related data.
  4. Restore the project from archive.

Archived Non-Project Data:

  1. Use SharePoint backup and recovery (2010) to create a clone of the current farm.
  2. Restore the archived items from Administrative backup and restore (see previous procedure).
  3. Export the user related data.

Step 8 - Find and save attachments, views, and VBA files

To find attachments and views, we recommend that you export a given project to XML. To do this, open it in Project Professional, and then save it as an XML file. Once you have XML files for the Projects that you want to review, see Find customized user items in Project Online and Project Server user export data.

Running the PowerShell scripts

The table below shows the parameters required for a given script. Run each script in a SharePoint Management Shell as a farm administrator.

Script Parameters
ResourcePlanTimephasedData201x.ps1
Export-SavedTaskStatusUpdates201x.ps1
ExportTaskStatusUpdateHistory201x.ps1
ProjectServerURL
ResId
OutputPath
PromptForCredential
UseWebLogin
Sync-ProjectWorkspace201x.ps1 ProjectServerURL
ProjectId
PromptForCredential
UseWebLogin
Export-UserViewSettings201x.ps1 ProjectServerURL
ResId
OutputPath

These parameters are described in the following table.

Parameter Description
ProjectServerURL URL of the PWA site
ResId Resource Id of the user
OutputPath Location to store the export files.
ProjectId Project workspace to synchronize

Also include one of the following authorization parameters each time you run a script:

Auth Parameter Description
[nothing passed in] Authenticate using NTLM and the Kerberos protocol as the current user.
PromptForCredential Authenticate using Basic or digest protocol or using NTLM and/or Kerberos with a different user.
UseWebLogin Authenticate using Forms and ADFS/SAML protocol.

For example:

.\Export-UserViewSettings2016.ps1 -ProjectServerURL "https://pwa" -resId "55efd6ff-853c-4fec-8abd-6df2c90b94e5" -OutputPath "C:\"

See each PowerShell script file for further examples and information about the parameters.

Running Export-SavedTaskStatusUpdates201x.ps1 (2010 and 2013 only)

To run the Export-SavedTaskStatusUpdates201x.ps1 script, you must run as a delegate of the user being exported in order to view the saved assignment. Use the following procedure:

  1. Turn delegation on in Project Server
  2. Enable delegation permissions on the user being exported
  3. Enable delegation permissions on yourself to delegate as that user. (As an admin you may already have permissions.)
  4. Configure yourself as a delegate of the user being exported
  5. Log in to Project Web App.
  6. Click the gear icon, and then click Act as a delegate.
  7. Start a delegate session.
  8. Run the Export-SavedTaskStatusUpdates201x.ps1 PowerShell script.
  9. Stop the delegate session.

See also

Delete user data from Project Server