Thursday, December 01, 2011

SQL Server 2008 - Dumping VARBINARY data to a file

I couldn't find a way to convert varbinary column data to a file using SQL Server 2008 Management Studio, so I did some googling and found info on using the bcp.exe command to do it (I believe this comes with the Management Studio install). In my case, I was extracting a PDF file stored as varbinary:

c:\temp>bcp "select MYVARBINARYCOL from MYTABLE where id = 1234" queryout "c:\filename.pdf" -S MYSQLSERVER\MYINSTANCE -T

Enter the file storage type of field filedata [varbinary(max)]:
Enter prefix-length of field filedata [8]: 0
Enter length of field filedata [0]:
Enter field terminator [none]:

Do you want to save this format information in a file? [Y/n] n

Starting copy...

1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 15 Average : (66.67 rows per sec.)

I used the -T option to use windows authentication to connect to the DB. If you use password auth, you'll need to use the -U and -P switches to specify a username and password.

3 comments:

Chris Tillman said...

It works! Thanks so much for your posting!!!

Sean Buchanan said...

Very helpful thank you!

Hồ Duy said...

It works ! Thank you so much