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