Friday, May 3, 2013

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
-- To update the currently configured value for advanced options.
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
-- To update the currently configured value for this feature.
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:
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. 

No comments:

Post a Comment