How to recover a deleted image from SQL Server into a file

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

1. 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

2. 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;
   Integrated Security=SSPI;"
   
   SqlConnection connection = new SqlConnection(conString);
   
   SqlCommand cmd = new SqlCommand("SELECT  ImageData FROM Images WHERE 
   ImageId = 1"connection);
   
   connection.Open();
           
   SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.
   Default);
   
               if (reader.Read())
               {
                   byte[] fileData = (byte[])reader.GetValue(0);
   
                   using (System.IO.FileStream fs = new System.IO.FileStream(
                       @"D:\Test\myimage.jpg"
                       System.IO.FileMode.Create,
                       System.IO.FileAccess.ReadWrite))
                   {
                       using (System.IO.BinaryWriter bw = new
   System.IO.BinaryWriter(fs))
                       {
                           bw.Write(fileData);
                           bw.Close();
                       }
                   }
   
               }

3. 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:

1. Launch ApexSQL Recover

2. 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

   

3. Connect to the server and database from which you would like to recover the images

   

4. Specify the folder to which you would like the images to be extracted to

   

5. 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



Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.

 

Related posts:

1. How to recover data that is missing or damaged as a result of a SQL injection attack
2. Recover deleted SQL data from a backup or from online database files
3. How to recover deleted SQL data from transaction logs
4. 4 techniques for recovering lost tables due to DROP Table operation
5. How to recover a single table from a SQL Server database backup
February 10, 2014