Export user data from Project Server 2019
Important!: The process to export user data from Project Server 2019 is very different from the process used for Project Server 2016, Project Server 2013, and Project Server 2010. To learn how to export user data from previous versions of Project Server, see Export user data from Project Server.
Process Overview
The following is an overview of the process to export a specific user's information from a Project Web App site in Project Server 2019:
Download the export scripts: Download the .sql and Microsoft PowerShell scripts for exporting user data.
Find the PWA sites in your environment: Find a listing of Project Web App instances in your Project Server farm.
Export workspace items for the user: Look for user data in project sites.
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.
Perform an export of the user's data: Export the information that you want to review by using the scripts.
Review your exported content: Look through the exported data for information about your user.
Archived items: Look for data about your user in the Archived database.
Find and save custom views, custom filters, attachments, and macros: Locate custom items.
Data you need to manually export: Look for user data not included in the export.
Step 1 - Download the export script files
Download the export scripts from the Microsoft Download Center.
Important notes about running the export scripts:
Run the .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:
In File Explorer, go to the location where you saved the zip file.
Right click on the zip file, and click Properties.
On the General tab, select Unblock.
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 |
Step 3 - Export workspace items for the user
Run the ExportWorkspaceItemsByDisplayName2019.sql script and search for data using possible display names of the user (partial name searches).
Note: You need to run the ExportWorkspaceItemsByDisplayName2019.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 database for the related PWA site. 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 | 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. |
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 FindUser2019.sql SQL script to find the user's Resource ID or claims account.
Provide values for the following parameters in the script:
| Parameter | Description |
|---|---|
| @siteID | 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 - Export your user's data from the PWA site
Next, you will need to run the** ExportProjectUserContent** PowerShell script to export your user's data from each PWA site in your Project Server environment. To run the script, you need to make sure you and your environment meet the prerequisites.
Prerequisites
- Project Online Desktop Client or Project Professional 2019 Public Preview: You will need the Project Online Desktop Client or Project Professional 2019 and be connected to the Project PWA instance.
To connect your Project client to your Project PWA instance:
Click the **File **tab to open the Backstage view. Click Info, and then click Manage Accounts.
In the Project Web App Accounts dialog box, click Add.
In the Account Properties dialog box, type a name for this account in the Account Name box.
Enter the URL of the PWA site you are connecting to in the Project Server URL box.
Click OK.
In the Project Web App Accounts dialog box, select Set as Default, and then click OK.
Restart Project, and log on to the PWA site.
Permissions: In order have the required permissions to run the script, you need to do at least one of the following:
Add yourself as a site collection admin to the PWA Site for which you are running the script.
If you are in Project permission mode, be assigned Manage Users and Groups and the Access Project Server Reporting Service permissions on the Project Server instance. If you are in SharePoint permission mode, be in the Global admin or SharePoint admin role.
Run the ExportProjectUserContent script
Use the ExportProjectUserContent.ps1 PowerShell script to export your user's data.
You will need to configure four parameters when running the script.
| -URL | URL of the PWA site |
|---|---|
| -ResourceID | Resource ID of the user. |
| -ClaimsAccount | Claims account of the user |
| -OutputDirectory | Location to store the export files. |
You will also need to choose the authentication method.
| Parameter | Description |
|---|
| Authentication 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. |
You can choose to run the script either by specifying the user's Resource ID or login Name.
To run the ExportProjectUser script using the users Resource ID
You would use the following command in Powershell with the paramaters listed above:
.\ExportProjectUserContent.ps1 -Url <PwaSiteURL> -ResourceUid <UsersResourceID> -OutputDirectory <LocationToStoreOutput>
For example, if you want to export user data from the Costoso PWA1 site (site URL of https://contoso/sites/pwa1) for a user with a Resource ID of cb5c91cf-fd6b-e711-80d0-00155da4a406, and have the export files save to c:\pwa1siteOutput, you would enter:
.\ExportProjectUserContent.ps1 -Url https://contoso/sites/pwa1 -ResourceUid cb5c91cf-fd6b-e711-80d0-00155da4a406 -OutputDirectory c:\pwa1siteOutput
To run the ExportProjectUser script using the users Claim Account
You would use the following command in Powershell with the paramaters listed above:
.\ExportProjectUserContent.ps1 -Url <PwaSiteURL> -ClaimAccount <UsersClaimAccount> -OutputDirectory <LocationToStoreOutput>
For example, if you want to export user data from the Costoso PWA1 site (site URL of https://contoso/sites/pwa1) for a user with a Login Name of AdamB@contoso.onmicrosoft.com, and have the export files save to c:\pwa1siteOutput, you would enter:
.\ExportProjectUserContent.ps1 -Url https://contoso/sites/pwa1 -LoginName AdamB@contoso.onmicrosoft.com -OutputDirectory c:\pwa1siteOutput
After the script runs successfully, all exported data will be stored in the -OutputDirectory you specified.
Select specific feature-related user data files to export
Some of the exported user content you receive will include a number of json formatted files that includes feature-specific user information. For example, the Security.json file contains data about the user's security groups, categories, and permissions settings. These feature-related json files are described in more detail in the next section. By default, you will receive all 27 feature-related json files when you run the ExportProjectUserContent script. However, you can use the -Options parameter to select specific json files to download. These include the following:
| -Options values | Json files you receive |
|---|---|
| All | All feature-related json files, all project-specific json files, and all project-list files. |
| Engagements | Engagements_page#.json |
| Resources | Resource.json, ReportingResource.json |
| Portfolio | BusinessDrivers.json, DriverPrioritizations.json, PortfolioAnalyses.json |
| Projects | DraftProjectList.xml , PublishedProjectList.xml. ReportingProjectList You will also receive one of each of the following for each project that the user was a part of: Project_projName_draft.json, Project_projName_draft.mpp, Project_projName_draft.xml, Project_projName_published.json, Project_projName_ published.mpp, Project_projName_ published.xml, Project_projName_reporting.json, Project_projName_reporting_Tasks, Project_projName_reporting_Assignments, Project_projName_reporting_Resources, Project_projName_reporting_Baselines, Project_projName_reporting_TaskTimephased, Project_projName_reporting_AssignmentTimephased, Project_projName_reporting_TaskBaselineTimephased, Project_projName_reporting_ AssignmentBaselineTimephased |
| ResourcePlans | ResourcePlans_page#.json, ReportingResourcePlans.json |
| Security | Security.json |
| ServerSettings | CustomFields.json, LookupTables.json, Calendars.json, Delegations.json, QueueJobs.json, SubscribedReminders.json, UnsubscribedAlerts.json, ReminderEmails.json, AdminAudit.json |
| Timesheets | Timesheets_Reporting.json, Timesheets_page#.json For the Timesheets_page#.json, you will get file per page. |
| TaskStatus | Rules.json, TaskStatus_AssignmentsHistory_page#.json, TaskStatus_AssignmentsSaved.json, TaskStatus_AssignmentsSubmitted.json |
| StatusReports | StatusReports.json |
| Workflow | Workflow.json |
| WorkspaceItems | WorkspaceItems.json |
| UserViewSettings | UserViewSettings.json |
Using the -Options parameter can be helpful if you want to export user data from the PWA site for specific features. For example, if you are only concerned with your user's data in the Portfolio Analysis feature, you can run the -Options parameter with the value of Portfolio:
.\ExportProjectUserContent.ps1 -Url https://contoso/sites/pwa1 -ResourceUid cb5c91cf-fd6b-e711-80d0-00155da4a406 -OutputDirectory c:\pwa1siteOutput -Options Portfolio
This will allow you to export the three json files that contain your user's data that pertains to the Portfolio Analysis feature (BusinessDrivers.json, DriverPrioritizations.json, PortfolioAnalyses.json).
Step 6 - Review your exported content
After you run the ExportProjectUserContent PowerShell script successfully, you will have the following output in the output directory you specified when running the command:
Project list files - You will receive three .xml files that provide a list of projects contained in the Project Draft and Published schemas in which the user was a part of. This means the user was involved in the project as at least one of the following:
Was the project owner.
Has a task assigned to him or her in the project.
Is an assignment owner of a task in the project.
Is the status manager of a task in the project.
These three .xml files are:
| Name | Description |
|---|---|
| DraftProjectList.xml | List of projects from the Draft schema that corresponds to the conditions above. |
| PublishedProjectList.xml | List of projects from the Published schema that corresponds to the conditions above. |
| ReportingProjectList.xml | List of projects from the Reporting schema that corresponds to the conditions above. |
The list of projects may differ slightly for each of the three .xml files. For example, a user can save the project but not publish, meaning that it will appear in the DraftProjectList.xml file, but not the PublishedProjectList.xml or ReportingProjectList.xml files.
A project admin can use the Project list .xml files to give them information about which project-specific export files they be interested in analyzing to decide how much of the exported content should be shared with the user.
All three of the ProjectList.xml files will have the following properties for each project listed:
| Property | Description |
|---|---|
| SiteId | The unique identifier for the PWA site in which the project exists. |
| Proj_UID | The unique identifier for the project. |
| Proj_Name | Name of the project. |
- Feature-related files - For each PWA site that the user is part of, the following feature-specific .json files will be exported to the specified output directory. The feature-specific files will contain user data as it pertains to the feature use throughout the PWA site. For example, the Drivers.json file will include data about Portfolio Analysis business drivers the user created or owned. If the user has no data relating to the feature on the specific PWA site, the file will contain no data.
The feature-specific .json files include:
| Name | Description |
|---|---|
| AdminAudit | Project Web App server settings change data. |
| BusinessDrivers | Portfolio analysis business drivers data. |
| Calendars | Enterprise calendar data. |
| CustomFields | Custom field data. |
| Delegations | Delegation data. |
| DriverPrioritizations | Business driver prioritizations data. |
| Engagements | Resource engagement data. |
| LookupTables | Lookup table data. |
| PortfolioAnalysis | Portfolio analyses data. |
| QueueJobs | Data about user jobs process through the Queue Service. |
| ReminderEmails | Reminder email data. |
| ReportingResourcePlans | Resource reporting data. |
| Resource | Resource data. |
| ResourcePlans | Resource plan data. |
| Rules | Rules data. |
| Security | Data about security groups, categories, and permissions. |
| StatusReports | Status report data. |
| SubscribedReminders | Subscribed reminders data. |
| TaskStatus_AssignmentsHistory | Statusing assignments history data. |
| TaskStatus_AssignmentsSaved | Statusing assignments save data. |
| TaskStatus_AssignmentsSubmitted | Statusing assignments submit data. |
| Timesheets | Data about timesheets. |
| Timesheets_Reporting | Reporting data about timesheets. |
| UnsubscribedAlerts | Unsubscribed alerts data. |
| UserViewSettings | User view settings data. |
| Workflow | Project workflow data. |
| WorkspaceItems | Data about SharePoint items from project sites. |
Certain feature-specific json files have the possibility of being large, so to improve performance, the following json files will spawn across multiple files:
Engagements.json
ResourcePlans.json
Timesheets.json
TaskStatus_AssignmentHistory.json
Note: To learn more about the objects contained in each of the feature-specific .json files, see the Feature-specific data section of Project Online and Project Server export data definitions.
Project-specific files - If the user is part of any project, then for each of those projects, several individual files will be exported to the output directory. This will happen if the user is part of the specific project as one of the following:
The project owner
Has a task assigned to him or her in the project
Is an assignment owner of a task in the project
Is the status manager of a task in the project
Project-specific data differs from the Feature-related data in that the data is specific to a single project. Feature-related data can include user data across many projects in the PWA site that the user was a part of, but pertaining to a single feature.
Note: For all project-specific files you receive, they will be prefixed with the specific project's Project Name. For example, if a project has a Project Name of Project1, all project-specific files we describe in this section will be prefixed with Project1.
For each project the user is a part of, you will received the following three sets of files:
- An .xml file for the project from the draft and published databases:
| Name | Description |
|---|---|
| <projectName>_draft.xml | The project file from the draft schema saved as .xml format. |
| <projectName>_published.xml | The project file from the published schema saved as .xml format. |
Note: See the Project XML Data Interchange Scheme Reference to understand the Project XML data contained in these files.
- An .mpp file for the project from the draft and published databases:
| Name | Description |
|---|---|
| <projectName>_draft.mpp | The project file from the draft schema saved as a Project .mpp file. |
| <projectName>_published.mpp | The project file from the published schema saved as a Project .mpp file. |
Note
You can open the .mpp file with Project Professional 2016, Project Professional 2019, or the Project Online Desktop client. Saving the exported .mpp files back to Project Online or Project Server is not supported.
- Eight .json files for the project from the reporting schema:
| Name | Description |
|---|---|
| Project_<projectName>_reporting_AssignmentBaselineTimephased.json | Assignment Baseline Timephase data for the project from the reporting schema. |
| Project_<projectName>_reporting_AssignmentTimephased.json | Assignment Timephase data for the project from the reporting schema. |
| Project_<projectName>_reporting_ProjectBaseline.json | Project Baseline data for the project from the reporting schema. |
| Project_<projectName>_reporting_Tasks.json | Project tasks data for the project from the reporting schema. |
| Project_<projectName>_reporting_Assignments.json | Assignment resources data for the project from the reporting schema. |
| Project_<projectName>_reporting_Resources.json | Resources data for the project from the reporting schema. |
| Project_<projectName>_reporting_TaskBaselineTimephased.json | Task baseline timephased data for the project from the reporting schema. |
| Project_<projectName>_reporting_TaskTimephased.json | Task timephased data for the project from the reporting schema. |
Note: To learn more about the objects contained in each of the .json files, see the Project-specific data filessection of Project Online export json object definitions.
- Three .json files with the project's metadata from the draft, published, and reporting schemas:
| Name | Description |
|---|---|
| <projectName>_draft.json | Project metadata file from the Draft schema |
| <projectName>_published.json | Project metadata file from the Published schema |
| <projectName>_reporting.json | Project metadata file from the Reporting schema |
Note: To learn more about the objects contained in each of the .json files, see the Project-specific Metadata files section of Project Online and Project Server export data definitions.
Step 7 - Archived items
ExportArchievdData2019.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:
Export the user related data.
Restore the project from archive.
Archived Non-Project Data:
Use SharePoint backup and recovery to create a clone of the current farm.
Restore the archived items from Administrative backup and restore (see previous procedure).
Export the user related data.
Step 8 - Find and save custom views, custom filters, attachments, and macros
After receiving the exported user content, you can use your data to find the user's custom views, custom filters, custom tables, attachments, and macros. To find these, you will need to have the MPP and XML file for each project in which you want to search. For more information on how to do this, see Find customized user items in Project Online and Project Server user export data.
Considerations for master and inserted projects
As noted earlier, the export script will only export projects that the user was a part of as an owner, has an assigned task, is an assignment owner of a task, or is the status manager of a task. When the user is part of an inserted project, but not the master project, only the inserted project will be exported. Similarly, if the user is only part of a master project and not any of the inserted projects, only the master project will be exported.
When saving a master project that a user was a part of, you will not need to save any associated inserted projects if you are prompted.
Step 9 – Data you need to manually export
Project Author
The author of the project is not exported using the above steps. You can run ExportProjectAuthor2019.sql to get the list of projects whose author matches the user display name or the users claims.
| Parameter | Description |
|---|---|
| @siteID | The PWA site ID for the site in which you want to find if the user is author of a project. |
| @searchName | The display name or claims of the Project Server user. |