Export Multiple Blob Files Data From Sql Table to Physical Location Using PowerShell

BLOB Data can be in a Audio file, pdf file, doc file, jpg/ png/ any image file of data types which can be loaded to a Sql table and saved in a Sql Database.

I have Imported or loaded some jpg files and pdf files into a Table in Sql Database to save the data. Generally this data loading to Sql table will be done by application team , if they need the data to be exported from a Sql table application will contact us .

I have exported this Blob data using PowerShell Script which is simple to use , for this approach used Ado.Net to query the data and used binary writer to write the files on a drive

I have Created a Database ‘BLOB‘ and a table ‘dbo.BlobData‘ , inserted some jpg and pdf files into the table .

From above screenshot you can see both PDF and JPG files in a Table with 27 rows. Now I am going to export those all files to a physical Location in one go.

I want to export the files to below shown location, currently we don’t see any files:

Below is the PowerShell Script I used to export the data , you need to change Parameters like InstanceName, DatabaseName, Destination Path and SELECT Statement .

## You can export LARGE Blob to file            
## with GetBytes-Stream.         
# In the below Code you need to change some parameters which are needed to get successfull export of blob files from a Table to disk.
$Server = "DESKTOP-02JIB76";         # Change to your Instance Name.            
$Database = "Blob";                  # Change to your Database Name
$Dest = "C:\temp\Blob\Export\";             # Change Path here.            
$bufferSize = 8192;               # Stream buffer size in bytes.            
# Select-Statement for name & blob            
# with filter.            
$Sql = "SELECT [PictureName]
        FROM dbo.BlobData";            
# Open ADO.NET Connection            
$con = New-Object Data.SqlClient.SqlConnection;            
$con.ConnectionString = "Data Source=$Server;" +             
                        "Integrated Security=True;" +            
                        "Initial Catalog=$Database";            
# 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);            
       $start += $received;            
       # Read next byte stream            
       $received = $rd.GetBytes(1, $start, $out, 0, $bufferSize - 1);            
# Closing & Disposing all objects            
Write-Output ("Finished");

I have executed above script in a PowerShell and all 27 files are exported to our destination location :

In below screenshot you can see all 27 files are successfully exported from a table :

We can also get this Blob data out by some other methods like below :

. Export Data using BCP T-Sql from SSMS but this required xp_cmdshell needs to be ENABLED

. Export Data using BCP Command from Command Prompt.

. Export Data using OLE Processes which needs Ole Automation Procedures to be ENABLED

4 responses to “Export Multiple Blob Files Data From Sql Table to Physical Location Using PowerShell”

  1. Hi, im trying to use your script but im getting the errors bellow, any chance of helping ?
    Exception calling “GetBytes” with “5” argument(s): “Invalid attempt to GetBytes on column ‘description’. The GetBytes function can only be used on columns of
    type Text, NText, or Image.”
    At line:35 char:5
    + $received = $rd.GetBytes(1, $start, $out, 0, $bufferSize – 1);
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidCastException

    Exception calling “GetString” with “1” argument(s): “Unable to cast object of type ‘System.Guid’ to type ‘System.String’.”
    At line:28 char:5
    + Write-Output (“Exporting: {0}” -f $rd.GetString(0));
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidCastException


  2. Hi there, I get the following error: Exception calling “GetString” with “1” Arguments: “Data is Null, This method or property cannot be called on Null Values”

    Any assistance greatly appreciated.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: