Script to export SQL Server Blob data with ADO.Net via PowerShell

Below is a shamelessly copied script from Olaf Helper over at TechNet.

If you have a fully configured FileStream SQL Server, Database and Table Column with the FileStream attribute then the following script is much better and avoids possible bugs with buffers and byte reading.  Check that out here on TechNet

I have reproduced here almost entirely for my own purpose of not having to search endlessly for it when I need to grab some records from older SQL database columns that are holding blob data in a varbinary(MAX).

# Configuration data 
$Server = ".";             # SQL Server Instance, local. 
$Database = "DBname"; # Database 
$Dest = "F:\Export\";     # Path to export to. 
$bufferSize = 8192;         # Stream buffer size in bytes.

# Select-Statement for name & blob 
$Sql = "
SELECT [Nom], [Data]
FROM Table
WHERE Value = 1
"; 
 
# Open ADO.NET Connection 
$con = New-Object Data.SqlClient.SqlConnection; 
$con.ConnectionString = "Data Source=$Server;" + 
 "Integrated Security=True;" + 
 "Initial Catalog=$Database"; 
$con.Open(); 
 
# New Command and Reader 
$cmd = New-Object Data.SqlClient.SqlCommand $Sql, $con; 
$cmd.CommandTimeout = 120 
$rd = $cmd.ExecuteReader(); 
 
# Create a byte array for the stream. 
$out = [array]::CreateInstance('Byte', $bufferSize) 
 
# Looping through records 
While ($rd.Read()) 
{ 
 try 
  { 
   Write-Output ("Exporting: {0}" -f $rd.GetString(0)); 
   # New BinaryWriter 
   $fs = New-Object System.IO.FileStream ($Dest + $rd.GetString(0)), Create, Write; 
   $bw = New-Object System.IO.BinaryWriter $fs; 
 
   $start = 0; 
   # Read first byte stream 
   $received = $rd.GetBytes(1, $start, $out, 0, $bufferSize - 1); 
   While ($received -gt 0) 
   { 
    $bw.Write($out, 0,      $received); 
    $bw.Flush(); 
    $start += $received; 
    # Read next byte stream 
    $received = $rd.GetBytes(1, $start, $out, 0, $bufferSize - 1); 
   } 
   $bw.Close(); 
   $fs.Close(); 
  } 
  catch 
  { 
   Write-Output ($_.Exception.Message) 
  } 
  finally 
  { 
   $fs.Dispose();         
  }
 }  
   
# Closing & Disposing remaining objects 
$rd.Close(); 
$cmd.Dispose(); 
$con.Close(); 
 
Write-Output ("Finished");
Read-Host -Prompt "Press Enter to exit"

All credit to the original authors and contributors

Published by Niall