SQL Server 2005/2008 : Export Blob or Image datatype from database.

Please refer to my previous blog post on how insert blob or images into SQL Server.

http://saveadba.blogspot.com/2012/02/import-blob-images-tsql.html

There are a few VB scripts to extract or export blob/image data from SQL Server but I prefer the TSQL method. You need to enable the 'Ole Automation Procedures' sp_configure option. This option is disabled by default.

Found a nice script at the following location
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101754

To enable Ole automation Procesdures use the following commands.

exec sp_configure 'show_advanced_options', 1
Reconfigure
go

exec sp_configure 'Ole Automation Procedures',1
Reconfigure
go


Use the following TSQL to extract all the blobs or images.
DECLARE @SQLIMG VARCHAR(MAX),
    @IMG_PATH VARBINARY(MAX),
    @TIMESTAMP VARCHAR(MAX),
    @ObjectToken INT

DECLARE IMGPATH CURSOR FAST_FORWARD FOR
--The following line will select which record/image you want to extract.
--So change the following line to select your record.
SELECT document from Images where id=15
       
OPEN IMGPATH

FETCH NEXT FROM IMGPATH INTO @IMG_PATH

WHILE @@FETCH_STATUS = 0
    BEGIN
        --Set the path where you want to save the file and also the extension of the file.
        --The file name will be the timestamp at which it was extracted.
        SET @TIMESTAMP = 'C:\Pictures\Test\' + replace(replace(replace(replace(convert(varchar,getdate(),121),'-',''),':',''),'.',''),' ','') + '.JGP'

        PRINT @TIMESTAMP
        PRINT @SQLIMG

        EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
        EXEC sp_OASetProperty @ObjectToken, 'Type', 1
        EXEC sp_OAMethod @ObjectToken, 'Open'
        EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH
        EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TIMESTAMP, 2
        EXEC sp_OAMethod @ObjectToken, 'Close'
        EXEC sp_OADestroy @ObjectToken

        FETCH NEXT FROM IMGPATH INTO @IMG_PATH
    END

CLOSE IMGPATH
DEALLOCATE IMGPATH

If you have saved the file name, extension and the original path in the table then use that in the @TIMESTAMP variable to set the path and file name where you want to save it.