I would like to retreive some binary data from a varbinary(max) column in a SQL Server database for debugging purposes.
What is the easiest way to get this data into a local binary file, preferably without having to write a throw-away console application?
I have tried using SQL Server Management Studio (with the "results to file" option) but this outputs a hex encoded binary string to the file, rather than the raw binary data.
I've found this solution with bcp command (run from command prompt):
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.
But I also like LinqPad suggestion in Robb Sadler's answer and somehow prefer it.