stuart.watson

Exporting AF Databases using PowerShell

Blog Post created by stuart.watson Champion on Feb 13, 2018

We recently had an issue with some inadvertent deletions from our AF database.

Recovering was challenging - and it would have been so much easier if we had had a plain export of our database, that we could have imported into a new database and copied out the parts we needed.

 

This Powershell script will do just that.

It will export all databases on a server, to named and timestamped files.

We have scheduled a task to run this once a week, as a means of archiving changes.

 

param (
    [string]$server = $env:COMPUTERNAME,                      # AF or PI server
    [string]$dbName = $null,     #Specific Database
    [string]$outDirectory = $null, 
    [string]$outRoot = "AFDBExport"
)

# Load the OSIsoft AF SDK
$refToMyAssembly = [reflection.assembly]::loadwithpartialname("OSIsoft.AFSDK")

# All of the AF Servers
$afServers = New-Object OSIsoft.AF.PISystems
$afServer = $afServers[$server]


if ( $dbName ) {
    $dbSet = $afServer.Databases[$dbName]


} else {
    $dbset = $afServer.Databases
}


$expMode = [OSIsoft.AF.PIExportMode]::AllReferences
$expMode = $expMode -bor [OSIsoft.AF.PIExportMode]::Security
$expMode = $expMode -bor [OSIsoft.AF.PIExportMode]::Flat
$expMode = $expMode -bor [OSIsoft.AF.PIExportMode]::SimplifiedConfigStrings
$expMode = $expMode -bor [OSIsoft.AF.PIExportMode]::DefaultValues


if ( $outDir -eq $null ) {
    if ( $PSScriptRoot -eq $null ) {
        $outDir = split-path -parent $MyInvocation.MyCommand.Definition
    } else {
        $outDir = $PSScriptRoot
    }
}
# The Date Part is set once per run
$datePart = Get-Date -Format 'yyyyMMddhhmmss'


foreach ( $db in $dbset ) {
    $childPath = $outRoot + '-' + $db.Name + '-' + $datePart + '.xml'
    [System.IO.Path]::GetInvalidFileNameChars() | % {$childPath = $childPath.replace($_,'_')} # Clean up the filename
    $filename = join-path -Path $outDir -ChildPath $childPath
    Write-Host "Exporting ""$db"" to ""$filename"""
    #
    $afServer.ExportXML($db,  $expMode, $filename, $null, $null, $null)
}

Outcomes