How to extract data from a SQL Server 2008 FILESTREAM field without using .NET

Question

I have a SQL Server 2008 database, with a VARCHAR(MAX) FILESTREAM column. I can load a record into it using this syntax:

`INSERT INTO DocumentRepository(DocumentExtension, DocumentName, Document) 
 SELECT
 'doc' AS DocumentExtension
 , 'Hamlet.doc' AS DocumentName
 , * FROM OPENROWSET(BULK 'D:\Docs\Hamlet.doc', SINGLE_BLOB) 
   AS Document;`

This loads my word document just fine. What I'd like to know is how to extract the data back to disk using SQL Server supplied tools. Might be T-SQL, bcp, etc, don't really care I would just like to know a way to do this without resorting to having to write a .NET application.

I've experimented with bcp, using the -c, -n, -N, and -w switches and it will extract the document but it's not formatted correctly. When I open it in Word there is a lot of binary gibberish at the beginning, and while the text is there all formatting is gone.

Thanks!

Answer 1

Great question - Basically you can't - you could select the path and access the file directly from the file system

But from Books online you will notice the operations are

• insert
• update
• query
• back up

FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data.

Answer 2

I feel a bit embarrassed to admit this, given that I wrote the FILESTREAM whitepaper for Microsoft - but I don't know the answer. My feeling is the same as UndertheFold's - you can't do it. I had a poke about to see if I could find any way of doing it with SSIS but I couldn't.

Answer 3

As UndertheFold states, you can't.

However, it is important to realize that renaming or deleting files directly in the filesystem will corrupt your db (Source). I'm not sure about editing the files, but personally I wouldn't risk it.