Home > SQL Server > Download binary data from SQL Database to File – SQL Server

Download binary data from SQL Database to File – SQL Server


Storing binary data in SQL Database is very simple and useful in many ways. For instance, We can store images in the database and show it in the reports. This is an interesting way to show data to customer with images.

SQL Server has 3 data types that can be used to store binary data.

  • binary
  • varbinary
  • image – This data type will be deprecated from future version of SQL Server. Use varbinary instead.

Implementation

I have already stored 2 jpg files in a table tblImages in SQL Server database. Now, I need to extract the stored image to my local disk. So, I am going to use BCP to download images from tblImages table.

BCP command

pic1Download Image from the database

Command:

BCP “select image from tblImages where ImageID=1” 
queryout {Filename with extension}
-S {SQL server instance name}
-d {Database name}
-T

Execution

pic2

Now, the image file is downloaded to the hard drive.

We can also use other methods to download binary date from database to hard drive.

Advertisements
  1. Hans
    April 15, 2014 at 2:29 am

    Hi, this was very helpfull and clearly explained! Thank. Hans

  2. Ian
    April 6, 2016 at 2:54 pm

    I tried the above code and it did indeed work … to a point. For some reason the JPG image has 4 preceding characters that cause it to be invalid. I removed these with a text editor and it worked.

    Any idea what these 4 characters could be and how to get rid of them in the export?

    They are:
    2B 90 01 00

    Thanks
    Ian

  1. No trackbacks yet.

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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: