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

    SSIS data flow task showing data source and  the extract column

    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

    ApexSQL Recover opening dialog

  2. Click on Extract BLOBs from a database

    Selecting Extract BLOBs from a database option

    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

    Choosing the Recover deleted BLOBs option

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

    Selecting the database to recover BLOB data from

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

    Specifying the output folder

  5. Click on the Recover button

    Dialog showing that recovery has been completed

    All images in the database will be extracted to the specified location. Including images which may have recently been deleted

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

    Dialog showing extracted BLOBs

    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

    Converting BLOB file to image file by renaming *.blob extension to *.jpg

    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.

     

    February 10, 2014