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:

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. Export workspace items for the user: Look for user data in project sites.

4. 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.

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

6. Review your exported content: Look through the exported data for information about your user.

7. Archived items: Look for data about your user in the Archived database.

8. Find and save custom views, custom filters, attachments, and macros: Locate custom items.

9. 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:

  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. 

  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:

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:

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:

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:

1. Click the **File **tab to open the Backstage view. Click Info, and then click Manage Accounts.

2. In the Project Web App Accounts dialog box, click Add.

3. In the Account Properties dialog box, type a name for this account in the Account Name box.

4. Enter the URL of the PWA site you are connecting to in the Project Server URL box.

5. Click OK.

6. In the Project Web App Accounts dialog box, select Set as Default, and then click OK.

7. 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:

  1. Add yourself as a site collection admin to the PWA Site for which you are running the script.

  2. 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 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.

You will also need to choose the authentication method.

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:

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:

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:

• 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:

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:

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:

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:

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.

Archived Project Data: To export archived projects:

1. Archive the current project.

2. Restore the archived version.

3. Export the user related data.

4. Restore the project from archive.

Archived Non-Project Data:

1. Use SharePoint backup and recovery 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 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.