fastest way to export blobs from table into individual files

Question

What is the fastest way to export files  blobs  stored in a SQL Server table into a file on the hard drive   I have over 2 5 TB of files  90 kb avg  stored as varbinary and I need to extract each one to a local hard drive as quickly as possible   BCP seems to work but it will take over 45 days with the speed I m seeing  and I m worried that my script will fail at some point because Management Studio will run out of memory

User · Answer

I came here looking for exporting blob into file with least effort. CLR functions is not something what I'd call least effort. Here described lazier one, using OLE Automation:

declare @init int
declare @file varbinary(max) = CONVERT(varbinary(max), N'your blob here')
declare @filepath nvarchar(4000) = N'c:\temp\you file name here.txt'

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, @file; -- Calling a method
EXEC sp_OAMethod @init, 'SaveToFile', NULL, @filepath, 2; -- Calling a method
EXEC sp_OAMethod @init, 'Close'; -- Calling a method
EXEC sp_OADestroy @init; -- Closed the resources

You'll potentially need to allow to run OA stored procedures on server (and then turn it off, when you're done):

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'Ole Automation Procedures', 1;  
GO  
RECONFIGURE;  
GO

User · Answer

I tried using a CLR function and it was more than twice as fast as BCP   Here s my code   Original Method   SET  bcpCommand    bcp  SELECT blobcolumn FROM blobtable WHERE ID       CAST  FileID AS VARCHAR 20        queryout       FileName      -T -c  EXEC master  xp cmdshell  bcpCommand   CLR Method   declare  file varbinary max     select blobcolumn from blobtable WHERE ID    fileid  declare  filepath nvarchar 4000    N c  temp      FileName SELECT Master dbo WriteToFile  file   filepath  0    C  Code for the CLR function  using System  using System Data  using System Data SqlTypes  using System IO  using Microsoft SqlServer Server   namespace BlobExport       public class Functions              SqlFunction        public static SqlString WriteToFile SqlBytes binary  SqlString path  SqlBoolean append                          try                     if   binary IsNull  amp  amp   path IsNull  amp  amp   append IsNull                                   var dir   Path GetDirectoryName path Value                          if   Directory Exists dir                               Directory CreateDirectory dir                             using  var fs   new FileStream path Value  append   FileMode Append   FileMode OpenOrCreate                                 byte   byteArr   binary Value                  for  int i   0  i  lt  byteArr Length  i                                          fs WriteByte byteArr i                                                 return  SUCCESS                         else               NULL INPUT                     catch  Exception ex                                return ex Message

User · Answer

For me what worked by combining all the posts I have read is  1 Enable OLE automation - if not enabled sp configure  show advanced options   1    GO   RECONFIGURE    GO   sp configure  Ole Automation Procedures   1    GO   RECONFIGURE    GO  2 Create a folder where the generated files will be stored  C  GREGTESTING  3 Create DocTable that will be used for file generation and store there the blobs in Doc Content  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    INSERT  dbo   Document    Extension    FileName     Doc Content        SELECT  pdf    SHTP Notional hire - January 2019 pdf   0x     varbinary blob   Important note  Don t forget to add in Doc Content column the varbinary of file you want to generate  4 Run the below script DECLARE  outPutPath varchar 50     C  GREGTESTING     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  i   COUNT 1  FROM  Doctable     WHILE  i  gt   1     BEGIN      SELECT       data    Doc Content        fPath    outPutPath          Doc Num         FileName        folderPath    outPutPath         Doc Num  FROM  Doctable WHERE id    i  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     5 The results is shown below

[sql] fastest way to export blobs from table into individual files

Examples related to sql

Examples related to sql-server-2008-r2

Examples related to blob