Export Image Column to Files in SQL Server – Step-by-Step Guide

blobdatafileexportmigrationsql server

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?

Best Answer

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]

Related Question