Use Powershell to export SQL Blob column data

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.

This is how we can use PowerShell to export SQL Blob data to file.

## Export of "larger" Sql Server Blob to file            
## with GetBytes-Stream.         
# Configuration data            
$Server = ".\SQL105CTP3";         # SQL Server Instance.            
$Database = "ToDO";            
$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 = '.xlsx'";            
            
# 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");

Server and Database Name is hard coded in this script. To make it parametrize, add these in first line;

param ($servername='.\SQL105CTP3', $databasename = 'ToDo',  $envname='Dev')
       

and then change these variables to get the value from parameters;

$Server = $servername;         # SQL Server Instance.            
$Database = $databasename;

Save the script and run it like this;

.\BLOBimagesCopy.ps1 -servername '(local' -databasename 'ToDO' -envname 'dev' 

Resources

https://social.technet.microsoft.com/wiki/contents/articles/890.export-sql-server-blob-data-with-powershell.aspx

https://www.red-gate.com/simple-talk/sysadmin/powershell/how-to-use-parameters-in-powershell/

Add to favorites