More and more companies are creating online catalogues to allow customers to browse their objects prior to coming to their store
Having a list with a price and description just doesn’t cut it anymore. People want to see what the product looks like and as such having images associated to each item is pivotal
This brings the following questions to mind:
- How do I get an image into my database?
- How do I extract an image from my database into a file?
- If someone accidently deleted an image, how can I retrieve it?
In this article I am going to give you some quick ways to get images in and out of your database, and then I will show you what you can do to recover a deleted image from your SQL Server database
Inserting an image into your SQL Server database
The easiest and quickest way to get an image into your database is to use the OPENROWSET T-SQL command
Here is some sample T-SQL code which imports an image into your SQL database
INSERT INTO ImagesStore SELECT 'C:\Users\Public\Pictures\Sample Pictures', BulkColumn FROM OPENROWSET( BULK 'C:\Users\Public\Pictures\Sample Pictures\Penguins.jpg',
Single_Blob) as ImageData
Extracting an image from your SQL Server database
Getting an image out of your database into a file, is not quite as easy. If the image still exists in your database the following three methods can be used to extract an image from your SQL database
Use sp_OA procedures
You can extract an image using sp_OA procedures. But this requires you to enable ‘Ole Automation Procedures’, which you may not want to do
sp_configure 'Ole Automation Procedures' ,1 GO RECONFIGURE GO DECLARE @token int, @image varbinary(max), @file varchar(50) SELECT @image = ImageData, @file= ExtractPath FROM ImagesStore WHERE
ImageId = 1 EXEC sp_OACreate 'ADODB.Stream', @token OUTPUT EXEC sp_OASetProperty @token, 'Type', 1 EXEC sp_OAMethod @token, 'Open' EXEC sp_OAMethod @token, 'Write', NULL,@image EXEC sp_OAMethod @token, 'SaveToFile', NULL, @file , 2 EXEC sp_OAMethod @token, 'Close' EXEC sp_OADestroy @token
Use code to extract an image
The most commonly used method is to write some code. By that I mean something like .Net or C++ etc. This is typically what it’s used by applications. But it’s not necessarily the easiest way to go if you just quickly need to recover an image, or if your coding skills are limited to T-SQL
Here is an example of how you can extract an existing image from your SQL Server database.
string conString = "Data Source=localhost;Initial Catalog=ImageDB;
SqlConnection connection = new SqlConnection(conString);
SqlCommand cmd = new SqlCommand("SELECT ImageData FROM Images WHERE
ImageId = 1"connection);
SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.
byte fileData = (byte)reader.GetValue(0);
using (System.IO.FileStream fs = new System.IO.FileStream(
using (System.IO.BinaryWriter bw = new
Use SSIS to extract an image
Another method you may use is SQL Server Integration Services. You need to create a data flow task, and then add a data source which selects both the image column and the file path column. This file path column must include the filename and file path of where you want the images to be extracted to. You then specify the extract column and the file path column in the Export Column transformation
Unfortunately SSIS, also cannot help you to get your image back if it has accidentally been deleted
How to extract a deleted image from SQL Server
The easiest method to recover a deleted image or a BLOB from a SQL database is by using ApexSQL Recover. ApexSQL Recover can extract both existing images and images which have been deleted
Here is how to do it:
Launch ApexSQL Recover
Click on Extract BLOBs from a database
Using this option all BLOBs will be extracted from the database. If you are specifically looking for deleted BLOBs, you may choose to use the recover Deleted BLOBs option
Connect to the server and database from which you would like to recover the images
Specify the folder to which you would like the images to be extracted to
Click on the Recover button
All images in the database will be extracted to the specified location. Including images which may have recently been deleted
If you look at the specified folder, you will see that all the BLOBs have been extracted with default names consisting out of the table name and a number. Since ApexSQL Recover does not have any other information about the BLOBs it has extracted, all the files have the file extension .blob.
Since we know that we were extracting an image and that the file type was .jpg, we can simply rename the .blob extension to .jpg and voila! We have our image extracted to a file
Please contact us for any problems or questions with the scripts.
February 10, 2014