Export SQL Server BLOB Data with PowerShell
In opposition to simple data like numbers and text, it's not possible to verify blob data for validity and correctness just by screening the binary dump.
Only when the respective application is accessing data it is may pointing to be bad data. In order to examine the data for accuracy, the DBA has to export them to open it with the appropriate program.
Blob data can be exported using PowerShell in a simple way, by querying the data with Ado.Net - SqlClient and then using a BinaryWriter to write it on local hard drive.
## Export of "larger" Sql Server Blob to file
## with GetBytes-Stream.
# Configuration data
$Server = ".\SQL105CTP3"; # SQL Server Instance.
$Database = "AdventureWorks2008";
$Dest = "D:\Export\"; # Path to export to.
$bufferSize = 8192; # Stream buffer size in bytes.
# Select-Statement for name & blob
# with filter.
$Sql = "SELECT [FileName]
,[Document]
FROM Production.Document
WHERE FileExtension = '.doc'";
# 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;
$rd = $cmd.ExecuteReader();
# Create a byte array for the stream.
$out = [array]::CreateInstance('Byte', $bufferSize)
# Looping through records
While ($rd.Read())
{
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();
}
# Closing & Disposing all objects
$fs.Dispose();
$rd.Close();
$cmd.Dispose();
$con.Close();
Write-Output ("Finished");