LLI Wiki — My Digital Knowledge Base

PowerShell: export a SQL Server blob to files

PowerShell script that exports SQL Server binary columns to files in chunks, using GetBytes and a FileStream.

This PowerShell script exports binary columns (varbinary(max), image) from SQL Server to files without loading each whole blob in memory. The first column of the query is the file name, the second column is the blob. Each blob is read in chunks with SqlDataReader.GetBytes() and written to a FileStream.

Script

## Export of "larger" Sql Server Blob to file
## with GetBytes-Stream.
# Configuration data
$Server = ".\SQLINSTANCE";        # 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");

Notes

Mastodon LLIMastodon SocialFosstodon