How do I use BCP or Sql Server Management Studio to get BLOB data out of Sql Server?

Question

I'm sorry if this question has been asked already, but I couldn't find it anywhere. I have a table that stores files as BLOBS. The column that holds the file is an image datatype. I would like to be able to extract the binary data out of the column and turn it in to an actual file. I would ideally like to be able to do this with BCP or management studio if possible.

I have tried BCP, but for some reason when I try and pull out an office document Word thinks it's corrupt. Here's what I've tried so far (obviously the values have been changed to protect the innocent :):

bcp "select document_binary_data from database where id = 12345" queryout "c:\filename.doc" -n -S server -U username -P password

This isn't working though? Any thoughts?

Edit Turns out you don't need the -n native flag. Also, BCP tries to include a 4 byte prefix by default on the image column - you actually want this set to 0.

bcp "select document_binary_data from database where id = 12345" queryout "c:\filename.doc" -S server -U username -P password

Enter the file storage type of field document_binary [image]:
Enter prefix-length of field document_binary [4]: 0
Enter length of field document_binary [0]:
Enter field terminator [none]:

Answer 1

If you can use C# / .NET code to do this, the following KB article may come in handy:

http://support.microsoft.com/kb/317016

Apparently you can do something like this with BCP and a format file, but IIRC the format file has to be pre-populated with the exact number of bytes it is expecting to pull from the column, which makes it quite impractical.

Another option you might choose is to use FILESTREAM in 2008 or, if you are not planning to migrate to 2008 anytime soon, store the documents on the file system and a pointer to them in the database. Yes there are pros and cons to this, but it's the way we've chosen in all projects to date.

Answer 2

I'm answering my own question since I'm getting annoyed with SO telling me to setup a bounty

Turns out you don't need the -n native flag. Also, BCP tries to include a 4 byte prefix by default on the image column - you actually want this set to 0.

bcp "select document_binary_data from database where id = 12345" queryout "c:\filename.doc" -S server -U username -P password

Enter the file storage type of field document_binary [image]:
Enter prefix-length of field document_binary [4]: 0
Enter length of field document_binary [0]:
Enter field terminator [none]: