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.)
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.