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]
              ,[PictureData]
        FROM dbo.BlobData";            
             
# 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");

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

Author: Sri

Hello Friends, This blog is to help the IT professionals who want to become a professional SQL Server DBA but don't know how and from where to start with. So, I am going to share my experiences and my learning in this blog. Will talk about what are the pre-requisite skills required to become a Professional SQL Server DBA, how much time it takes to be a good DBA and what are the additional skill sets are required to become a good DBA. Apart from that I will also post real time sql server settings on server level and database level, Configuring High Availability. Also will share the Client requirements with real time setups if possible I will provide you with screenshots.

4 thoughts on “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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: