Export Blob(BINARY or VARBINARY) From SQL Table And Save It As A File

binary value or type in SQL Server is a series of bytes (known as a byte array in some programming languages). Just like char/varchar, there are fixed-length types, binary(1-8000), and variable-length ones, varbinary(1-8000) and varbinary(max).

Sometimes we store this type of data in SQL Tables and lose the source files. This type of data is stored in a binary format which is system formatted. 
A PDF file is converted and stored in this format. And the text looks similar to this,
Now to recreate a file from this data is not something that can be created by simple copy-paste.
Here is the process which can be used to get the source file.
DECLARE @outPutPath varchar(50) = 'C:\ExtractedFiles'
, @i bigint
, @init int
, @data varbinary(max)
, @fPath varchar(max) 
, @folderPath  varchar(max)

--Get Data into temp Table variable so that we can iterate over it
DECLARE @Doctable TABLE (id int identity(1,1), [FileName]  varchar(100), [Doc_Content] varBinary(max) )

INSERT INTO @Doctable( [FileName],[Doc_Content])
Select [RecordID],[FileUpload] FROM  [dbo].[tbl_AuthorConferenceList]

--SELECT * FROM @table

SELECT @i = COUNT(1) FROM @Doctable

WHILE @i >= 1

        @data = [Doc_Content],
        @fPath = @outPutPath +  '\' +[FileName] +'.pdf',
        @folderPath = @outPutPath
       FROM @Doctable WHERE id = @i

  --Create folder first
  EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instance created
  EXEC sp_OASetProperty @init, 'Type', 1; 
  EXEC sp_OAMethod @init, 'Open'; -- Calling a method
  EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method
  EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method
  EXEC sp_OAMethod @init, 'Close'; -- Calling a method
  EXEC sp_OADestroy @init; -- Closed the resources

  print 'Document Generated at - '+  @fPath  

--Reset the variables for next use
SELECT @data = NULL 
, @init = NULL
, @fPath = NULL 
, @folderPath = NULL
SET @i -= 1
Now you might end up getting compilation issues.
The reason is all the System Stored Procedures are unavailable.
To solve this we have to enable the Facets Property "OleAutomatedEnabled"
Export Blob(BINARY or VARBINARY) From SQL Table
Export Blob(BINARY or VARBINARY) From SQL Table