Sql-server – How to extract data from a SQL Server 2008 FILESTREAM field without using .NET

sql serversql-server-2008

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!

Best Answer

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.

Related Topic