Export Documents saved as Blob / Binary from SQL Server

T-SQL Scripts to Export Blob or Binary data stored in SQL Server

I tried many ways to export files or documents saved as binary (Blob) datatypes in SQL Server. However, finally came up with below solution which worked very well. Below script was used to export around 25GB of files stored in SQL Server.

To understand this, lets create a table in Database which will store files from local system into SQL Server as binary / Blob .

CREATE TABLE [dbo].[Document](
	[Doc_Num] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[Extension] [varchar](50) NULL,
	[FileName] [varchar](200) NULL,
	[Doc_Content] [varbinary](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

How to Insert Blob into Database

For demo purpose, we will save two files in our table using below queries

INSERT [dbo].[Document] ([Extension] ,[FileName] , [Doc_Content] )
SELECT 'pdf', 'Salesforce Lightning.pdf',[Doc_Data].*
FROM OPENROWSET 
    (BULK 'C:\G2\My POC\Blog\SQL Server\Source\lightning.pdf', SINGLE_BLOB)  [Doc_Data]

INSERT [dbo].[Document] ([Extension] ,[FileName] , [Doc_Content] )
SELECT 'html', 'Progress.html',[Doc_Data].*
FROM OPENROWSET 
    (BULK 'C:\G2\My POC\Blog\SQL Server\Source\Progress.html', SINGLE_BLOB)  [Doc_Data]

If we try to see content in actual table, it will look like

Insert Blob into Database
Insert Blob into Database

Export Blob From SQL Server and save it as a file

For demo purpose, we want to save documents on local disc. We will use Doc_Num to be created as folder and document will be saved in that folder. To create folders using SQL Server, we will use stored procedure CreateFolder created in this post.
Note : We will create only one folder per document. If multiple nested folder needs to be created then we need to iterate through each folder and call CreateFolder stored procedure for each folder. Its explained in this blog post.

Now time to see actual T-SQL which will iterate through all documents, create folder and save Blob as a file on local disc.

USE [POC]
DECLARE @outPutPath varchar(50) = 'C:\G2\My POC\Blog\SQL Server\Extract Blob'
, @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), [Doc_Num]  varchar(100) , [FileName]  varchar(100), [Doc_Content] varBinary(max) )
 
INSERT INTO @Doctable([Doc_Num] , [FileName],[Doc_Content])
Select [Doc_Num] , [FileName],[Doc_Content] FROM  [dbo].[Document]
 
--SELECT * FROM @table

SELECT @i = COUNT(1) FROM @Doctable
 
WHILE @i >= 1
BEGIN 

	SELECT 
	 @data = [Doc_Content],
	 @fPath = @outPutPath + '\'+ [Doc_Num] + '\' +[FileName],
	 @folderPath = @outPutPath + '\'+ [Doc_Num]
	FROM @Doctable WHERE id = @i
 
  --Create folder first
  EXEC  [dbo].[CreateFolder]  @folderPath
  
  EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace 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
END

Variable @outPutPath stores root folder path, where folders will be created and Blob content would be exported into it as a file.

Below image shows output in action :

SQL Server Export Blob as File
SQL Server Export Blob as File

Related posts

15 thoughts on “Export Documents saved as Blob / Binary from SQL Server”

  1. Hi,
    I am trying to use your code to export varbinary from the database to file, but I am not seeing any folders being created or files been exported. No errors.
    So, will the files get created on the database server or on the machine on which we run this T-sql ?.
    Thanks for your help.

      1. Thanks for the response. Yes, the path does exists. And now when I uncommented the raiserror stmt from the [dbo].[CreateFolder] procedure ( I used your code for this too), I am getting below error-

        Msg 50000, Level 16, State 1, Procedure CreateFolder, Line 36

        On printing values for @init ,OLEfolder as seen in the below code –

        — check if folder exists

        EXEC @init=sp_OAMethod @OLEfilesytemobject, ‘FolderExists’, @OLEfolder OUT, @newfolder
        print @OLEfolder
        print @init

        —-Results—-
        0
        0

        Msg 50000, Level 16, State 1, Procedure CreateFolder, Line 36
        This tell me, that the sp_OAMethod is not able to find the folder .
        Please advise. Thanks again !

      2. Hi,

        I tried to get this working and everything seems to process fine without no errors, I get Documents generated at – but when i navigate to the folder; the folder structure has been created e.g. there is 4 attachments so folder 1,2,3,4 have been created but there is nothing in the folders.

        Please help!!!!

  2. Thank you Jitendra, exactly what I was looking for.
    Just applied it to create a demo about “Compressed” data using the Compress functionality.
    Saving a columns value as a file (extension = .zip). 7-zip flawlessly extracts the data within to it’s original uncompressed value.

  3. Hi Jitendra,

    For me above query got executed and stored the attachments in my given path also but attachment size is showing 0 Bytes

  4. Good Heavens – this is truly great. I really appreciate google (and obviously this kind of results and your efforts). THANK YOU!

  5. Hi,

    I can run the scripts successfully but when i go into the folders the scripts have created there is nothing in the folders. The contents are not being exported.

    Please help!

  6. Hi,

    I can run the script successful and it creates the folder structures but it does not have any contents in the folder. I am using a local SQL database for testing purposes.

    Please help!

  7. Thank you Jitendra, it really helped me creating files from BLOB data.

    I am able to create file on specified path but the I am not able to open the file. Below is the message appears when double click on the file,

    We’re sorry. We can’t open “File_Name” because we found a problem with its contents.

    Details,
    The file is corrupt and cannot be opened.

    Thanks,
    Shardul Pathak

  8. Hi Jitendra,

    I am creating file from BLOB data using SQL, file created successfully but not able to open the file. Below is the message appears when trying to open the file,

    We’re sorry. We can’t open “file_name” because we found a problem with its contents.

    Details, The file is corrupt and cannot be opened.

  9. It’s seem work with role system admin. If I just want to export by normal SQL admin. How can I do that?
    Because I don’t want to grant full permission for some guys want to export file.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.