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

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]

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.

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