Script to save varbinary data to disk

The BCP approach does not work for me. The bytes it writes to disk cannot be deserialized back to the .net objects I stored. This means that the bytes on disk aren't equivalent to what's stored. Perhaps BCP is writing some kind of header. I'm not sure.

I found the following code here at the bottom of the article. It works great! Although it was intended for stored BMP images, it works with any varbinary.

DECLARE @SQLIMG VARCHAR(MAX),    @IMG_PATH VARBINARY(MAX),    @TIMESTAMP VARCHAR(MAX),    @ObjectToken INTDECLARE IMGPATH CURSOR FAST_FORWARD FOR         SELECT csl_CompanyLogo from mlm_CSCompanySettingsLocalizationsOPEN IMGPATH FETCH NEXT FROM IMGPATH INTO @IMG_PATH WHILE @@FETCH_STATUS = 0    BEGIN        SET @TIMESTAMP = 'd:\' + replace(replace(replace(replace(convert(varchar,getdate(),121),'-',''),':',''),'.',''),' ','') + '.bmp'        PRINT @TIMESTAMP        PRINT @SQLIMG        EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT        EXEC sp_OASetProperty @ObjectToken, 'Type', 1        EXEC sp_OAMethod @ObjectToken, 'Open'        EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH        EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TIMESTAMP, 2        EXEC sp_OAMethod @ObjectToken, 'Close'        EXEC sp_OADestroy @ObjectToken        FETCH NEXT FROM IMGPATH INTO @IMG_PATH     END CLOSE IMGPATHDEALLOCATE IMGPATH

I am adding this to build on JohnOpincar's answer, so that others who want to use LinqPad can get a working solution faster.

/*This LinqPad script saves data stored in a VARBINARY field to the specified folder.1. Connect to SQL server and select the correct database in the connection dropdown (top right)2. Change the Language to C# Program3. Change "Attachments" to the name of your table that holds the VARBINARY data4. Change "AttachmentBuffer" to the name of the field that holds the data5. Change "Id" to the unique identifier field name6. Change "1090" to the identity of the record you want to save7. Change the path to where you want to save the file. Make sure you choose the right extension.Notes: Windows 10 may give you "Access Denied" error when trying to save directly to C:\. Rather save to a subfolder.*/void Main(){    var context = this;    var query =         from ci in context.Attachments        where ci.Id == 1090        select ci.AttachmentBuffer    ;    byte[] result = query.Single().ToArray();    File.WriteAllBytes(@"c:\DEV\dumpfile.xlsx", result);    Console.WriteLine("Done");}

You can use BCP, not T-SQL, but works well.

BCP "SELECT FileContent FROM table WHERE ID = 1" queryout "C:\file.txt" -T