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?

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]

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

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