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.
This question is related to
sql
sql-server-2008-r2
blob
The answer is
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;
}
}
}
}
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 >= 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:
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
Source: Stackoverflow.com