HowTo: Archive a Work Area

Be sure to check out my Scripting4Crypto initiative. It’s a fun way to get into using cryptocurrencies all while getting your PowerShell needs met.

In this post, we will be “Archiving” a Work Area from a ProjectWise datasource to a SQLite database. The end result of this process is a SQLite database containing nearly all of the data related to the Work Area being archived. This includes the Work Area definition(s), Environment definition(s), folders, documents, document audit trail, and more. This is a near complete snapshot of the Work Area at the time of archiving. This does not include the access control applied to the Work Area. However, this information can be exported to an Excel file and stored with the SQLite database. The physical files are converted to blobs (binary large objects) and inserted into the SQLite database which eliminates dealing with many individual folder and files.

Keep in mind, there are other options to migrate your data. See another post for copying folders from one datasource to another. (Link) This may be a better solution for your specific individual needs.

NOTE: This does require a 32-bit session of PowerShell.  Also, the account used for this process must be able to access all data in the Work Area. It is recommended to turn off access control to eliminate any potential missed data, and to also speed up the process.

We will be using the following cmdlets to accomplish this task. All of the ProjectWise related cmdlets are available using the PWPS_DAB module. At the time of this post, I am using version 1.12.8.0. Take a look at the help for each of the cmdlets to become familiar with their functionality, available parameters, etc.

• Get-PWFolders
• Get-PWDocumentsBySearch
• Export-PWDocumentsToArchive
• Export-PWProjectAccessControl

Create and Populate Variables

Here we will create and populate the required variables. Each variable corresponds to a parameter from the Export-PWDocumentsToArchive cmdlet. Except for the ExcelFileName. This variable will be used to export the Access Control data to an Excel workbook.

#region CREATE AND POPULATE VARIABLES

<# This ProjectWiseFolder variable specifies the path to the 
    ProjectWise Work Area to be exported. #>
$ProjectWiseFolder = 'Projects\BSI900 - Adelaide Tower'
<# The OutputFolder variable specifies the local folder
    to be used during the exporting process. #>
$OutputFolder = 'd:\ArchivedProjects'
# The OutputFileName variable specifies the name of the SQLite database to be created.
$OutputFileName = "BSI900 - Adelaide Tower_$(Get-Date -Format yyyyMMdd).sqlite"
<# The ExportFileName variable specifies the file name of the Excel 
    workbook to be created. This is for exporting the access control. #>
$ExportFileName = "BSI900 - Adelaide Tower_$(Get-Date -Format yyyyMMdd).xlsx"
<# The following two switch parameters are optional.
    Include ONLY if you want to EXCLUDE audit trail data. #>
$NoAuditTrail = $false
$ExcludeSecondaryAuditTrail = $false

#endregion CREATE AND POPULATE VARIABLES

Get Folder and Document Counts

Before I archive the Work Area, I like to gather some information pertaining to the Work Area. Here, I will only get the folder and document counts for comparison after the export.  However, it is a good idea to generate some reports containing additional Work Area information such as Access Control (which will will be doing), reference information for MicroStation dgn files.  I will demonstrate a couple of ways to get the object counts.

#region GET FOLDER AND DOCUMENT COUNTS

# Get Folder counts for Work Area to be archived using the Measure-Object cmdlet.
$FolderCount = Get-PWFolders -FolderPath $ProjectWiseFolder | 
Measure-Object | Select-Object -ExpandProperty Count
# Get document counts by using the .Count property of the returned objects.
$DocumentCount = (Get-PWDocumentsBySearch -FolderPath $ProjectWiseFolder).Count

# I like Green!
Write-Host "Counts" -ForegroundColor Green
Write-Host " - Folder : $FolderCount" -ForegroundColor Green
Write-Host " - Document : $DocumentCount" -ForegroundColor Green

#endregion GET FOLDER AND DOCUMENT COUNTS

Export To Archive

Now we will export the Work Area to a SQLite database archive. This could be run as a single line in your console without all of the other steps I’ve outlined.  However, if you are scripting and validating, etc., the more information you have the better off you are. My opinion of course.

Here we will use a Splat to define the Parameter / Value pairs using the variables we previously created and populated.

#region EXPORT TO ARCHIVE

$Splat_Export = @{
    ProjectWiseFolder = $ProjectWiseFolder
    OutputFolder = $OutputFolder
    OutputFileName = $OutputFileName
    NoAuditTrail = $NoAuditTrail
    ExcludeSecondaryAuditTrail = $ExcludeSecondaryAuditTrail
}
Export-PWDocumentsToArchive @Splat_Export -Verbose

#endregion EXPORT TO ARCHIVE

The following shows the splat prior to exporting the Work Area.

Export Access Control

The final step in the process is to export the Access Control associated with the Work Area. There are a couple of different ways to export this data, however, we will be using the Export-PWProjectAccessControl cmdlet. Again, we will be using Splatting to define the Parameter / Value pairs.

#region EXPORT ACCESS CONTROL

$Splat_Access = @{
    FolderPath = $ProjectWiseFolder
    ExportFilePathName = "$OutputFolder\$ExportFileName"
}
Export-PWProjectAccessControl @Splat_Access -Verbose

#endregion EXPORT ACCESS CONTROL

The following shows the local folder after the exporting process has completed. We end up with two files, one contains the archived data, the other contains the access control data.

SQLite Database

The generated SQLite database contains loads of information. The number of tables returned will vary based on the Work Area / Folder(s) being exported and the options selected.

NOTE: I use SQLiteStudio (3.1.1) to open the sqlite database file.

The following shows the contents of the SQLite database.

General_AuditTrail

The General_AuditTrail table contains all of the document audit trail records based on the options selected when exporting.

General_Documents_Table

The General_Documents_Table table contains all of the general metadata associated with each document exported to the archive. Such as the document name, create date, created by, etc.

General_EnvironmentAttributes

The General_EnvironmentAttributes table contains the document attributes metadata for each document exported.

General_Environments

The General_Environments table contains a list of the included ProjectWise Environments. These are the environments applied to the folders within the Work Area.

General_Folders_Table

The General_Folders_Table table contains all of the general metadata associated with each folder exported to the archive. Such as the name, environment, storage, etc.

General_ProjectProperties

The General_ProjectProperties table contains the Work Area properties associated with all Work Areas exported to the archive.

General_ProjectTypes

The General_ProjectTypes table contains a list of all Work Area types associated with the Work Areas exported.

General_Resource_ZipFiles / General_Resources

The General_Resource_ZipFiles / General_Resources tables contain information pertaining to any resources associated with a Work Area which was exported. The General_Resources table contains a list of the resources, while the General_Resources_ZipFiles table contains the corresponding zipfile stored as a blob.

General_RichProjectProperties

The General_RichProjectProperties contains all Work Area properties for any Work Areas exported with a Work Area type associated.

General_RichProjects

The General_RichProjects table lists all of the Work Areas exported.

General_Users

The General_Users table contains user information for all users related to the work area exported.

General_Workflows

The General_Workflows table contains any workflow and states which were used within the Work Area exported.

General_ZipFiles

The General_ZipFiles table is probably the most important table. This one contains all of the documents which have been placed into zip files to compress them, and then stored as blobs. Each of the OriginalDocumentGuids correspond to a document within the General_Documents_Table.

Access Control

The following shows the output for the Export-PWProjectAccesControl cmdlet.

---

The following is a link to the full PowerShell script. 

HowTo-ArchiveWorkArea

Experiment with it and have fun. Hopefully, you find this useful. Please let me know if you have any questions or comments.  If you like this post, please click the Like button at the bottom of the page.