la manière la plus rapide d'exporter des blobs à partir de la table dans des fichiers individuels
Quel est le moyen le plus rapide pour exporter des fichiers (blobs) stockés dans une table SQL Server dans un fichier sur le disque dur? J'ai plus de 2.5 TB de fichiers (90 kb avg) stockés comme varbinary et je dois extraire chacun à un disque dur local aussi vite que possible. BCP semble fonctionner, mais cela prendra plus de 45 jours avec la vitesse que je vois, et je m'inquiète que mon script va échouer à un moment donné parce que Management Studio va manquer de mémoire.
3 réponses
j'ai essayé D'utiliser une fonction CLR et elle était plus de deux fois plus rapide que la BCP. Voici mon code.
Méthode Originale:
SET @bcpCommand = 'bcp "SELECT blobcolumn FROM blobtable WHERE ID = ' + CAST(@FileID AS VARCHAR(20)) + '" queryout "' + @FileName + '" -T -c'
EXEC master..xp_cmdshell @bcpCommand
méthode 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)
Code C# pour la fonction 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;
}
}
}
}
je suis venu ici à la recherche d'exporter blob dans le fichier avec le moins d'effort. Les fonctions CLR ne sont pas quelque chose que j'appellerais le moins d'effort. Ici décrit lazier one, en utilisant 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
vous aurez potentiellement besoin de permettre d'exécuter des procédures stockées OA sur le serveur (et ensuite de l'éteindre, lorsque vous aurez terminé):
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
utiliser une solution de programmation est une façon, mais la préoccupation dans la question originale qu'un script pourrait échouer si SSMS s'épuise de mémoire peut aussi être abordée en créant un travail D'Agent SQL pour la tâche. Ceci, bien sûr, ignore complètement la partie performance de la question.