DumpBrokerJsonBinaries.ps1

# Setup 

$publicationId = 20 # The publication where your content is published

# Broker database - substitute your own settings here. 
$connStringBuilder = new-object System.Data.SqlClient.SqlConnectionStringBuilder 
$connStringBuilder["Data Source"] = "WEB8,1433" 
$connStringBuilder["Initial Catalog"] = "Tridion_Broker" 
$connStringBuilder["User ID"] = "TridionBrokerUser" 
$connStringBuilder["Password"] = "Tridion1" 

$message = "JSON binary data for publication $publicationId"
$message
'=' * $message.Length


function DisplayOutput( $binaryId, $variantId, $path, $text) {
    Write-Output "Dumping JSON content for Binary: `"$binaryId`", Variant `"$variantId`""
    Write-Output "`t Path: `t$path"
    try {
        $json = ConvertFrom-Json $text
        ConvertTo-Json -Compress:$false $json
    }
    catch {
        "Ünable to parse JSON."
        $_.Exception.Message
        "==================="
        $text
    }

}

try {
    $conn = new-object System.Data.SqlClient.SqlConnection 
    $conn.ConnectionString = $connStringBuilder.ConnectionString 

    $conn.Open() 

    $comm = new-object System.Data.SqlClient.SqlCommand 
    $comm.CommandText = @"
    select bv.BINARY_ID
          ,bv.VARIANT_ID
          ,bv.PATH
          ,bv.URL
	      ,bc.CONTENT
      from BINARYVARIANTS bv 
      inner join BINARY_CONTENT bc 
      on bv.PUBLICATION_ID = bc.PUBLICATION_ID
      and bv.BINARY_ID = bc.BINARY_ID
      and bv.VARIANT_ID = bc.VARIANT_ID
      where bv.PUBLICATION_ID = $publicationId  
      and bv.TYPE = 'application/json'
"@

    $comm.CommandType = "Text"
    $comm.Connection = $conn 
    try {
        $reader = $comm.ExecuteReader() 
        $readResult = $reader.Read() 
        if (-not $readResult) { 
            "No record found"
        } 
        while ($readResult) { 
          $binaryId = $reader.GetInt32(0) 
          $variantId = $reader.GetString(1) 
          $path = $reader.GetString(2) 
          $url = $reader.GetString(3) 
          try {
            # It's actually Unicode, but then things get weird, so try lying.... 
            $contentStream = new-object IO.StreamReader($reader.GetStream(4), [Text.Encoding]::ASCII)
            $stringFromUnicode = $contentStream.ReadToEnd()
          
  
            $bytes = [Text.Encoding]::ASCII.GetBytes($stringFromUnicode) 
            $text = [Text.Encoding]::UTF8.GetString($bytes) 
          }  finally {$contentStream.Dispose()}

          DisplayOutput $binaryId $variantId $path $text

          $readResult = $reader.Read() 
        } 
    } finally {$reader.Close() }
} finally {$Conn.Close() }