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.

Answer 1

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 && !path.IsNull && !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 < byteArr.Length; i++)                 {                     fs.WriteByte(byteArr[i]);                 };             }             return "SUCCESS";           }           else              "NULL INPUT";         }         catch (Exception ex)         {                     return ex.Message;         }       }     } } 

Answer 2

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