Use BCP to export BLOBs to files

I came across a need to view all of the files stored in the BLOB (aka "image" column of a table in a SQL Server database. In this particular case, the files were all zip files, but the process should work the same for any type of file.

Step 1: Enable xp_cmdshell

This process relies on xp_cmdshell to run BCP. By default xp_cmdshell is disabled and must be enabled as follows:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

You probably want to disable xp_cmdshell again (set the value to 0) when you are finished using it.

Step 2: Create the bcpFormat.fmt file

Inspired by this response to this post by duckworth, I created a bcpFormat.fmt file with the following content:

8.0
1
1 SQLIMAGE 0 0 "" 1 Image ""

Make sure that there is an empty line at the end of the file (thanks to this post by Jon Raynor).

Step 3: Query the BLOB table

This step is the trickier ... run a simple SELECT query that uses string concatenation to generate SQL statements as output. Here is an an example:

select 'EXEC master.dbo.xp_CmdShell ''BCP "select blobField FROM blobTable where idField = ' + CAST(idField as varchar(10)) + '" QUERYOUT C:\TEMP\' + filenameField + ' -T -fC:\TEMP\bcpFormat.fmt''' from blobTable 

Some items to note:

• Make sure to use the proper field (blobField, idField, filenameField) and table names (blobTable)
• You could add a WHERE clause to filter the rows that you export
• Make sure that the output path and *.fmt file exist and have necessary permissions 

Step 4: Execute the BCP statements

After running the query in Step 3 in SQL Server Management Studio, do the following:

1. Select all of the rows in the result grid 
2. Copy
3. Open a new (empty) query window
4. Paste
5. Execute

All of the BCP commands will run and export all of the files to the designated folder.