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
AWESOME IT IS WORKING LIKE A GEM
Thank you very much!!!
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
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.