Nesse post eu gostaria de compartilhar algumas soluções de como compactar e descompactar arquivos e diretórios pelo SQL Server. Isso é especialmente útil para criar rotinas de ETL onde você precisa utilizar esses recursos utilizando scripts T-SQL ou mesmo para compactar arquivos de backup gerados na versão Express (que não possui suporte a backups compactados).
Como em algumas alternativas vou utilizar o 7-zip, vocês podem personalizar ainda mais os exemplos citados aqui consultando a documentação oficial do binário, bem completa e exemplificada.
Alternativa #1: Utilizando 7-zip e xp_cmdshell
Visualizar conteúdo
Uma forma simples de conseguir implementar esse recurso é utilizando o binário do 7-zip e o comando xp_cmdshell no SQL Server. Para utilizar esse recurso, você precisará ser membro da role sysadmin.
Para ativar o xp_cmdshell, basta executar o comando abaixo:
sp_configure 'advanced options', 1
RECONFIGURE
GO
sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO
Pré-requisito: 7-zip
Para que seja possível utilizar esse recurso, você precisará baixar os binários do 7-zip neste link. Você precisará baixar a versão “7-Zip Extra: standalone console version, 7z DLL, Plugin for Far Manager”, que vem no formato .7z.
Caso você não queira instalar o 7-zip no seu ambiente para poder abrir esse arquivo, vou disponibilizar os arquivos referentes ao standalone console version (versão 18.05) abaixo: Versão x64 (recomendável) | Versão x86
Após baixar os arquivos, descompacte-os em algum diretório à sua escolha. Para os exemplos desse artigo, escolhi o diretório “C:\Binn\”.
Como compactar arquivos e diretórios
Para compactar arquivos e diretórios utilizando 7-zip e xp_cmdshell, você pode utilizar a Stored Procedure abaixo, que vai facilitar a sua utilização no dia a dia.
USE [dirceuresende]
GO
CREATE PROCEDURE dbo.stpCompacta_Arquivo (
@caminho VARCHAR(500),
@filtro VARCHAR(500),
@arquivoCompactado VARCHAR(500),
@nivelCompactacao INT = 5,
@recursivo BIT = 0,
@senha VARCHAR(100) = NULL
)
AS
BEGIN
DECLARE @caminhoBinario VARCHAR(255) = 'C:\Binn\7za.exe'
DECLARE @argumentos VARCHAR(MAX) = 'a -tzip -mx' + CAST(@nivelCompactacao AS VARCHAR(2)) + ' "' + @arquivoCompactado + '"' + (CASE WHEN @recursivo = 1 THEN ' -r' ELSE '' END) + (CASE WHEN NULLIF(LTRIM(RTRIM(@senha)), '') IS NOT NULL THEN ' -p' + @senha ELSE '' END) + ' "' + @caminho + '\' + @filtro + '" -mmt';
DECLARE @Comando VARCHAR(8000) = 'call "' + @caminhoBinario + '" ' + @argumentos
-- PRINT @Comando
EXEC xp_cmdshell @Comando
END
Exemplo de uso
Neste exemplo, vou demonstrar como compactar todos os arquivos e sub-diretórios do diretório “C:\Temporario\”, e salvando no arquivo “C:\Teste\Arquivo.zip”, com nível de compactação médio (5):
/*
Compacta todos os arquivos com extensão .txt no diretório "C:\Temporario" (apenas na raiz, sem sub-diretórios),
usando compactação máxima (9) e protegendo o arquivo com a senha "dirceu"
*/
EXEC dbo.stpCompacta_Arquivo
@caminho = 'C:\Temporario', -- varchar(500)
@filtro = '*.txt', -- varchar(500)
@arquivoCompactado = 'C:\Teste\Arquivo.zip', -- varchar(500)
@nivelCompactacao = 9, -- int
@recursivo = 0, -- bit
@senha = 'dirceu' -- varchar(100)
Como descompactar arquivos e diretórios
Utilizando a Stored Procedure abaixo, podemos facilmente descompactar arquivos (com e sem senha) em algum diretório conforme a nossa necessidade. Vale ressaltar que, caso o diretório de destino não exista, ele será criado.
USE [dirceuresende]
GO
CREATE PROCEDURE dbo.stpDescompacta_Arquivo (
@arquivoCompactado VARCHAR(500),
@pastaDestino VARCHAR(500),
@filtro VARCHAR(500) = NULL,
@senha VARCHAR(100) = NULL
)
AS
BEGIN
DECLARE @caminhoBinario VARCHAR(255) = 'C:\Binn\7za.exe'
DECLARE @argumentos VARCHAR(MAX) = 'x "' + @arquivoCompactado + '" -aoa -o"' + @pastaDestino + '" ' + ISNULL(@filtro, '') + '' + (CASE WHEN NULLIF(LTRIM(RTRIM(@senha)), '') IS NOT NULL THEN ' -p' + @senha ELSE '' END) + ' -r -mmt';
DECLARE @Comando VARCHAR(8000) = 'call "' + @caminhoBinario + '" ' + @argumentos
PRINT @Comando
EXEC xp_cmdshell @Comando
END
Utilizando a mesma solução da alternativa #1, mas desta vez, encapsulados em procedures C# com o SQLCLR. A maior vantagem dessa solução é com relação ao permissionamento, que permite que você conceda apenas acesso de EXECUTE nessas procedures e o usuário, sem ser membro da role sysadmin, pode utilizar esses recursos.
Pré-requisito: 7-zip
Para que seja possível utilizar esse recurso, você precisará baixar os binários do 7-zip neste link. Você precisará baixar a versão “7-Zip Extra: standalone console version, 7z DLL, Plugin for Far Manager”, que vem no formato .7z.
Caso você não queira instalar o 7-zip no seu ambiente para poder abrir esse arquivo, vou disponibilizar os arquivos referentes ao standalone console version (versão 18.05) abaixo: Versão x64 (recomendável) | Versão x86
Após baixar os arquivos, descompacte-os em algum diretório à sua escolha. Para os exemplos desse artigo, escolhi o diretório “C:\Binn\”.
Código-fonte da solução
Abaixo, vou disponibilizar o código-fonte da solução de compactação utilizando 7-zip e SQLCLR.
Classe Retorno.cs (Exibir mensagens de alertas e erros)
using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
namespace Bibliotecas.Model
{
public static class Retorno
{
public static void Erro(string erro)
{
Mensagem(erro);
throw new ApplicationException(erro);
}
public static void Mensagem(string mensagem)
{
using (var conexao = new SqlConnection("context connection=true"))
{
conexao.Open();
using (var comando = new SqlCommand("IF ( (512 & @@OPTIONS) = 512 ) select 1 else select 0", conexao))
{
if ((int)comando.ExecuteScalar() != 0) return;
}
var retorno = SqlContext.Pipe;
retorno?.Send(mensagem.Length > 4000 ? mensagem.Substring(0, 4000) : mensagem);
}
}
public static void RetornaReader(SqlDataReader dataReader)
{
var retorno = SqlContext.Pipe;
retorno?.Send(dataReader);
}
}
public class Ret : Exception
{
public Ret(string str) : base(str)
{
}
}
}
stpCompacta_Arquivo.cs
using System;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
using System.Diagnostics;
using Bibliotecas.Model;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void stpCompacta_Arquivo(SqlString caminho, SqlString filtro, SqlString arquivoCompactado, SqlInt32 nivelCompactacao, SqlString senha)
{
string[] arquivos;
#region tratamento de entradas
if (nivelCompactacao.Value < 0 || nivelCompactacao.Value > 9)
{
Retorno.Erro("Parametro inválido na variavel nivelCompactacao, use de 0 a 9 para compactar, sendo 9 o nivel mais alto (5 recomendado).");
return;
}
try
{
if (!Directory.Exists(caminho.Value))
{
Retorno.Erro($"O caminho especificado '{arquivoCompactado}' não existe ou está inacessivel.");
return;
}
}
catch
{
Retorno.Erro($"Erro ao converter caminho especificado ('{arquivoCompactado}').");
return;
}
try
{
arquivos = Directory.GetFiles(caminho.Value, filtro.Value);
}
catch
{
Retorno.Erro($"Erro ao listar arquivos no caminho especificado ({arquivoCompactado}).");
return;
}
var objetoArquivoCompactado = new FileInfo(arquivoCompactado.Value);
var diretorioArquivoCompactado = objetoArquivoCompactado.Directory?.ToString();
if (diretorioArquivoCompactado == null || !Directory.Exists(diretorioArquivoCompactado))
{
Retorno.Erro($"O caminho do arquivo compactado especificado ({diretorioArquivoCompactado}) não existe ou inacessível.");
return;
}
if (objetoArquivoCompactado.Extension.Trim() != "")
{
try
{
objetoArquivoCompactado.Delete();
}
catch
{
Retorno.Erro($"o arquivo compactado especificado ({arquivoCompactado.Value}) está sendo usado.");
return;
}
}
else
{
Retorno.Erro($"O arquivo compactado especificado não possui uma extensão válida ({objetoArquivoCompactado.Extension.Trim()}).");
return;
}
#endregion
try
{
var argumentos = $@" a -tzip -mx{nivelCompactacao.Value} ""{arquivoCompactado.Value}""" + (!senha.IsNull && !string.IsNullOrEmpty(senha.Value) ? $" -p{senha.Value} " : " ") + $@"""{caminho.Value}\{filtro.Value}"" -mmt";
using (var scriptProc = new Process { StartInfo = { FileName = @"C:\Binn\7za.exe", UseShellExecute = false, Arguments = argumentos, RedirectStandardOutput = true, RedirectStandardError = true, StandardOutputEncoding = Encoding.GetEncoding(850), StandardErrorEncoding = Encoding.GetEncoding(850), CreateNoWindow = true } })
{
scriptProc.Start();
var output = scriptProc.StandardOutput.ReadToEnd();
var erro = scriptProc.StandardError.ReadToEnd();
if (output.Length > 0)
{
//Retorno.Mensagem(output);
}
if (erro.Length > 0)
{
Retorno.Erro(erro);
}
}
var quantidadeArquivos = arquivos.Length;
Retorno.Mensagem("Total de: (" + quantidadeArquivos + ") arquivos foram compactados em: " + arquivoCompactado.Value);
}
catch (Exception e)
{
Retorno.Erro("Erro : " + e.Message);
}
}
};
Alternativa #3: Utilizando a SharpZipLib e SQLCLR (C#)
Visualizar conteúdo
Para finalizar as alternativas desse post, gostaria de compartilhar uma solução utilizando a lib SharpZipLib, que permite compactar e descompactar arquivos utilizando apenas código C#, sem depender de binários externos como nos exemplos anteriores (7za.exe).
Como a biblioteca SharpZipLib é bem grande, acaba ficando inviável disponibilizar todo o código fonte aqui no post, além também, da versão T-SQL. Por este motivo, vou disponibilizar links para fazer o download dos arquivos de código-fonte.
A solução completa do Visual Studio, onde você poderá editar o código livremente, está disponibilizada neste link.
Neste link aqui estou disponibilizando o código-fonte T-SQL, para permitir a criação dos objetos sem precisar instalar e nem abrir o Visual Studio.
Para entender melhor o parâmetro @filtro dessa solução, uma vez que ele é diferente das alternativas anteriores (que utilizam o 7-zip), recomendo a leitura da documentação oficial da biblioteca.
Comentários (0)
Carregando comentários…