How to export an image column to files in SQL Server?

Question

I will migrate from a database. There is one column of type image that I would like to export to binary files on the file system. One file for each record. How can I do this with SQL Server?

• Aasim Abdullah: A good post by Amna Asif sqlcache.blogspot.com/2014/09/…
• Martin Smith: I'd probably knock up a simple SSIS dataflow with an Export Column Transformation

Answer 1

This is the solution I came up with:

1. Enable xp_cmdshell with

   EXEC sp_configure 'show advanced options', 1
   GO
   RECONFIGURE
   GO
   EXEC sp_configure 'xp_cmdshell', 1
   GO
   RECONFIGURE
   GO
   

2. If needed create a directory with xp_cmdshell to get the needed permissions.

   EXEC master..xp_cmdshell 'mkdir C:\exportdir'
   

3. Use BCP with queryout

   EXEC master..xp_cmdshell 'BCP "SELECT column_of_type_image FROM **your_db WHERE id = 1 " queryout "C:\exportdir\yourfile.pdf" -T -N'
   

**your_db must be the fully qualified table name, i.e [Yourdb].[YourSchema].[YourTable]

• user91489: 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 become invalid. I removed these with a text editor and voila ... JPG 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
• Hannah Vernon: What is the type of the column you exported from?

Answer 2

I had the same problem with the extra 4 bytes being added to the beginning of all of my files as well. Instead of using -N option in my bcp command, I changed it to -C RAW. When you do this, bcp will be prompted with the following questions:

Enter the file storage type of field FileData [image]:
Enter prefix-length of field FileData [4]:
Enter length of field FileData [0]:
Enter field terminator [none]: 
Do you want to save this format information in a file? [Y/n] 

To fix this I created a text file (i.txt) on the root of my sql server which had the following lines within it to answer each of these:

I
0
0
n

Then, my EXEC bcp line became:

EXEC master..xp_cmdshell 'BCP "SELECT column_of_type_image FROM **your_db WHERE id = 1 " queryout "C:\exportdir\yourfile.pdf" -T -C RAW<C:\i.txt'

This exported my file without any of the extra characters.

Answer 3

I came around looking for a solution to export an IMAGE Column having stored different kind of file types (pdf, xls, doc, xml ...) I want to export.

The approach in the answer worked for pdf files only. To export all kinds of files I had to adjust the solution as follows:

(1.) Create a format template file:

Declare @sql varchar(500); 
Declare @sql varchar(500); 
SET @sql = 'bcp db.dbo.ImgTable format nul -T -n -f C:\tmp\export.fmt -S ' + @@SERVERNAME; 
select @sql; 
EXEC master.dbo.xp_CmdShell @sql; 

(2.) Open the created export format file and edit it that way:

10.0
1
1 SQLIMAGE 0 0 "" 1 img_col ""

Then execute your export command:

EXEC master..xp_cmdshell 'BCP "SELECT IMG_COL FROM db.dbo.ImgTable WHERE id = ''CAB240C0-0068-4041-AA34-0000ECB42DDD'' " queryout "C:\tmp\myFile.xml" -T -f C:\tmp\export.fmt -S '

(3.) In case you run into this error (as I did):

"[Microsoft][SQL Native Client]Host-file columns may be skipped only when copying into the Server"

make sure the following:

• make sure you didn't forget to edit the second row and enter the number of entries here (1 in this scenario)!
• make sure you have a CRLF at the end of the last line, without this it did not work!

After this, the export of any IMAGE Column file word without an error.

Tributes for 1. and 2. all goes to the answer of the following question: https://stackoverflow.com/questions/1366544/how-to-export-image-field-to-file/24006947#24006947

Answer 4

If you have not any problem with a GUI solution, there is a really great add-in for SSMS SSMSBoost which provides plenty of useful features, and of course the simplest way to preview images stored in SQL(at least in my opinion)

NOTE : You must restart SSMS after installing this add-in.

Install it and enjoy previewing images just with : RightClick> Visualize As> Picture

Answer 5

Before proceeding, make sure the xp_cmdshell command is enabled, as it is disabled by default.

EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE
GO
EXEC master..xp_cmdshell 'mkdir D:\Project\Member\Images'

Keep the command on ONE LINE - SINGLE LINE!!!

SET @Command = 'bcp "SELECT Member_Picture FROM dbserver.[Member_Image] WHERE memberId = 1 " queryout "D:\Project\Member\Images\member1.jpg" -T -N ' 
PRINT @Command -- debugging
EXEC xp_cmdshell @Command
GO

• JustAMartin: On some servers xp_cmdshell is disabled and you get SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

Answer 6

USE [POC]
DECLARE @outPutPath varchar(50) = 'C:\Extract_Photos'
, @i bigint
, @init int
, @data varbinary(max)
, @fPath varchar(max)
, @folderPath varchar(max)
--Get Data into temp Table variable so that we can iterate over it
DECLARE @Doctable TABLE (id int identity(1,1), [Doc_Num] varchar(100) , [FileName] varchar(100), [Doc_Content] varBinary(max) )
INSERT INTO @Doctable([Doc_Num] , [FileName],[Doc_Content])
Select [STUDENTNO] , [STUDENTNAME],[STUDENTPHOTO] FROM [dbo].[STUDENTPHOTOS]
--SELECT * FROM @table
SELECT @i = COUNT(1) FROM @Doctable
WHILE @i >= 1
BEGIN
 SELECT
 @data = [Doc_Content],
 @fPath = @outPutPath + '\'+ [Doc_Num] + '\' +[FileName],
 @folderPath = @outPutPath + '\'+ [Doc_Num]
 FROM @Doctable WHERE id = @i
 --Create folder first
 EXEC [dbo].[CreateFolder] @folderPath
 EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created
 EXEC sp_OASetProperty @init, 'Type', 1;
 EXEC sp_OAMethod @init, 'Open'; -- Calling a method
 EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method
 EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method
 EXEC sp_OAMethod @init, 'Close'; -- Calling a method
 EXEC sp_OADestroy @init; -- Closed the resources
 print 'Document Generated at - '+ @fPath
--Reset the variables for next use
SELECT @data = NULL
, @init = NULL
, @fPath = NULL
, @folderPath = NULL
SET @i -= 1
END

• András Váczi: Please amend your post by explaining what this code is supposed to do.
• Vaas: Thanks Erax for the script it worked wonderfully. PS: The script fails to extract the file if ran remotely using SSMS. It had to be run on the SQLServer directly for it to pull the attachment.