SQL Server – How to Save an Image Column to File System?

querysql server

Imagine I have a table like this in MSSQL


-------------------------------------
file_name(varchar) | file_doc(image) |
--------------------------------------
A                  | image_binary1   |
--------------------------------------
B                  | image_binary2   |
--------------------------------------

I want to save contents of this table to file system something like the following

 
 - c:\somewhere\A.pdf 
 - c:\somewhere\B.pdf

how to achive that in MSSQL?

Best Answer

  1. The easiest way is to use SSIS: the Export Column component does what you are looking for. Here is an example: Export images from a SQL Server Table to a Folder with SSIS

    This is not only the easiest way, it requires minimal permissions: only SELECT permission on the table and access to file folder.

  2. Also you can write a powershell script: Export SQL Server BLOB Data with PowerShell

    The user should be able not only access a table (SELECT permission) but the rights to execute powershell scripts.

  3. It can be done using bcp but only for 1 file at a time, so for multiple files you need to write a cursor and use xp_cmdshell. Use of xp_cmdshell is not a best practice, it should be enabled on your server and you should have elevated permissions to be able to use xp_cmdshell. In case of bcp you should use format file, otherwise your files will contain additional 8 bytes and have a wrong format.
Related Question