I like to move it, move it. Upload and download binary data to or from SQL Server using standard tools

Imagine that you have a huge collection of Hello Kitty images (I do).
One day you make a decision to organize your collection - sort it, label it, tag it, remove duplicates and so on. The first thing you need - sophisticated data storage, of course. And it is obvious that you choose SQL Server - the world's best database platform :)
And since SQL Server is already present in our Universe - how about to load binary data into it?

Load data

What first comes into your mind when you hear "load data into SQL Server"? SSIS of course! Very powerful, very flexible, very.... Too "very" I guess. You need to develop SSIS package, and before you need to learn how you can develop SSIS package... I'm too old and lazy.
Second - as all of true developer - "I can develop my unique data load application!". Yes, you can. Next time.
Maybe there is a really simple solution for one-time ad-hoc data load task?

Do you remember? When you read MSDN you noticed OPENROWSET function which primary use "is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data by using OLE DB."
However, "OPENROWSET also supports bulk operations through a built-in BULK provider that enables data from a file to be read and returned as a rowset."
What that means for us? That means that we can read binary file from a disk :)

declare @rawdata varbinary(max)
select @rawdata = CAST(b as varbinary(max))  FROM OPENROWSET(BULK N'd:\hello\kitty.jpg', SINGLE_BLOB) a(b)

When you know how to load single file into variable - you know how to load all files.
Of course it's not all that simple - you cannot pass variable as a file name so you must use dynamic sql, SQL Server should have appropriate security permissions to access directory with file and so on, but all these problems can be easily solved.

Unload data

Ok. We successfully loaded data into database, sorted, cleared, all duplicates were removed - now time to unload images back to the disk.
How to do that?
Unfortunately there is no t-sql statement to accomplish this task. But we have BCP utilty!
To unload binary data to a file on disk you need:
1. Query that returns single line with single binary column

select KittyImage from KittyTable where id = 1

2. Simple format file (image.fmt) to strip unnecessary leading bytes from output:

9.0 
1 
1 SQLBINARY 0 0 "" 1 Data "" 

Now you can call BCP:

bcp "select KittyImage from KittyTable where id = 1" queryout kitty.jpg -T -n -f image.fmt

Done. You unloaded single image to file on the disk.
To unload multiple files I usually use T-SQL to generate content of the bat file and run this file.

That's all, folks!