Быстрый способ экспортировать капли из таблицы в отдельные файлы

Каков самый быстрый способ экспортировать файлы (капли), хранящиеся в таблице SQL Server, в файл на жестком диске? У меня более 2,5 ТБ файлов (90 kb avg), хранящихся в виде varbinary, и мне нужно как можно быстрее извлечь их на локальный жесткий диск. BCP, похоже, работает, но я получаю более 45 дней со скоростью, которую я вижу, и я беспокоюсь, что мой script в какой-то момент не сработает, потому что у Management Studio закончится память.

Ответ 1

Я попытался использовать функцию CLR, и она была более чем в два раза быстрее, чем BCP. Вот мой код.

Оригинальный метод:

SET @bcpCommand = 'bcp "SELECT blobcolumn FROM blobtable WHERE ID = ' + CAST(@FileID AS VARCHAR(20)) + '" queryout "' + @FileName + '" -T -c'
EXEC master..xp_cmdshell @bcpCommand

Метод CLR:

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)

Код С# для функции CLR

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;
        }
      }
    }
}

Ответ 2

Я пришел сюда, ища экспорт blob в файл с наименьшими усилиями. Функции CLR - это не то, что я бы назвал наименьшими усилиями. Здесь описано более ленивое, используя 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

Вероятно, вам нужно будет разрешить выполнение хранимых процедур OA на сервере (а затем выключить, когда все будет готово):

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

Ответ 3

Использование программного решения является одним из способов, но проблема в исходном вопросе о том, что script может выйти из строя, если у SSMS закончится нехватка памяти, можно также устранить, создав задание агента SQL для задачи. Это, конечно, полностью игнорирует исполнительную часть вопроса.