In this post I would like to share some solutions on how to compress and unzip files and directories through SQL Server. This is especially useful for creating ETL routines where you need to utilize these features using T-SQL scripts or even for compressing backup files generated in the Express version (which does not support compressed backups).
Since in some alternatives I will use 7-zip, you can further customize the examples cited here by referring to official binary documentation, well complete and exemplified.
One simple way to implement this feature is by using the 7-zip binary and xp_cmdshell command in SQL Server. To use this feature, you will need to be a member of role sysadmin.
To enable xp_cmdshell, simply run the command below:
| | sp_configure 'advanced options', 1 RECONFIGURE GO sp_configure 'xp_cmdshell', 1 RECONFIGURE GO |
Prerequisite: 7-zip
Before you can use this feature, you will need to download the 7-zip binaries. on this link. You will need to download the “7-Zip Extra version: standalone console version, 7z DLL, Plugin for Far Manager ”, which comes in .7z format.
If you do not want to install 7-zip in your environment in order to open this file, I will make the files for standalone console version (18.05 version) below:
X64 Version (recommended) | X86 Version
After downloading the files, unzip them in any directory of your choice. For the examples in this article, I chose the “C: \ Binn \” directory.
How to compress files and directories
To compress files and directories using 7-zip and xp_cmdshell, you can use the Stored Procedure below, which will make it easier to use on a daily basis.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | 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 |
Example of use
In this example, I will demonstrate how to compress all files and sub-directories in the “C: \ Temporario \” directory, and saving it in the “C: \ Test \ File.zip” file, with an average compression level (5):
| | EXEC dbo.stpCompacta_Arquivo @caminho = 'C:\Temporario\', -- varchar(500) @filtro = '*', -- varchar(500) @arquivoCompactado = 'C:\Teste\Arquivo.zip', -- varchar(500) @nivelCompactacao = 5, -- int @recursivo = 1 -- bit |
Result:
ZIP file generated:
Another examples:
| | -- Modo básico EXEC dbo.stpCompacta_Arquivo @caminho = 'C:\Temporario', -- varchar(500) @filtro = '*.txt', -- varchar(500) @arquivoCompactado = 'C:\Teste\Arquivo.zip' -- varchar(500) |
| | -- Compactando todos os arquivos que comecem com Teste% EXEC dbo.stpCompacta_Arquivo @caminho = 'C:\Temporario', -- varchar(500) @filtro = 'Teste*', -- varchar(500) @arquivoCompactado = 'C:\Teste\Arquivo.zip' -- varchar(500) |
| | -- Compactando o arquivo "Teste.txt" EXEC dbo.stpCompacta_Arquivo @caminho = 'C:\Temporario', -- varchar(500) @filtro = 'Teste.txt', -- varchar(500) @arquivoCompactado = 'C:\Teste\Arquivo.zip' -- varchar(500) |
| | /* 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) |
How to unzip files and directories
Using the Stored Procedure Below, we can easily unzip files (with and without password) into any directory as needed. Note that if the destination directory does not exist, it will be created.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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 |
Examples of use:
| | -- Exemplo básico EXEC dbo.stpDescompacta_Arquivo @arquivoCompactado = 'C:\Teste\Arquivo.zip', -- varchar(500) @pastaDestino = 'C:\Teste 2\' |
| | -- Descompactando arquivos com senha EXEC dbo.stpDescompacta_Arquivo @arquivoCompactado = 'C:\Teste\Arquivo.zip', -- varchar(500) @pastaDestino = 'C:\Teste 2\', -- varchar(100) @senha = 'dirceu' -- varchar(100) |
| | -- Descompactando apenas arquivos com extensão .txt EXEC dbo.stpDescompacta_Arquivo @arquivoCompactado = 'C:\Teste\Arquivo.zip', -- varchar(500) @pastaDestino = 'C:\Teste 2\', -- varchar(100) @filtro = '*.txt' -- varchar(100) |
| | -- Descompactando apenas o arquivo "Teste.txt" EXEC dbo.stpDescompacta_Arquivo @arquivoCompactado = 'C:\Teste\Arquivo.zip', -- varchar(500) @pastaDestino = 'C:\Teste 2\', -- varchar(100) @filtro = 'Teste.txt' -- varchar(100) |
Using the same solution as the #1 alternative, but this time, encapsulated in C # procedures with SQLCLR. The biggest advantage of this solution is in relation to permissionamento, which allows you to grant only EXECUTE access in these procedures and the user, without being a member of role sysadmin, can use these features.
If you do not know or do not know what is the SQLCLR, learn more by accessing the post Introduction to SQL Common Language Runtime (CLR) in SQL Server.
Prerequisite: 7-zip
Before you can use this feature, you will need to download the 7-zip binaries. on this link. You will need to download the “7-Zip Extra version: standalone console version, 7z DLL, Plugin for Far Manager ”, which comes in .7z format.
If you do not want to install 7-zip in your environment in order to open this file, I will make the files for standalone console version (18.05 version) below:
X64 Version (recommended) | X86 Version
After downloading the files, unzip them in any directory of your choice. For the examples in this article, I chose the “C: \ Binn \” directory.
Solution Source Code
Below I will provide the source code for the compression solution using 7-zip and SQLCLR.
Return.cs Class (Displaying Alert and Error Messages)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | 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_Archive.cs
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 | 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); } } }; |
stpDescompact_Archive.cs 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 | using System; using System.Data.SqlTypes; using System.Diagnostics; using System.IO; using System.Text; using Bibliotecas.Model; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void stpDescompacta_Arquivo(SqlString arquivoCompactado, SqlString pastaDestino, SqlString filtro, SqlString senha) { #region tratamento de entradas var ArquivoCompactado = new FileInfo(arquivoCompactado.Value); if (ArquivoCompactado.Directory == null || !Directory.Exists(ArquivoCompactado.Directory.ToString())) { Retorno.Erro($"O caminho do arquivo compactado especificado ({ArquivoCompactado.Directory}) não existe ou inacessível."); return; } if (!ArquivoCompactado.Exists) { Retorno.Erro($"O arquivo compactado '{arquivoCompactado.Value}' não existe."); return; } #endregion try { var argumentos = $@" x ""{arquivoCompactado.Value}"" -aoa -o""{pastaDestino.Value}"" {filtro.Value} " + (!senha.IsNull && !string.IsNullOrEmpty(senha.Value) ? $" -p{senha.Value} " : "") + "-r -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); } } Retorno.Mensagem("Os arquivos foram descompactados em: " + pastaDestino.Value); } catch (Exception e) { Retorno.Erro("Erro : " + e.Message); } } }; |
T-SQL source code
If you want to create the assembly and the Stored Procedures without having to change any source code and without having to even install Visual Studio, just use the T-SQL code below. Remember to extract the 7-Zip binaries in the “C: \ Binn \” directory (To change, you would have to edit the project and deploy it)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | USE [dirceuresende] GO ALTER DATABASE [dirceuresende] SET TRUSTWORTHY ON GO IF (OBJECT_ID('dbo.stpCompacta_Arquivo') IS NOT NULL) DROP PROCEDURE [dbo].[stpCompacta_Arquivo] GO IF (OBJECT_ID('dbo.stpDescompacta_Arquivo') IS NOT NULL) DROP PROCEDURE [dbo].[stpDescompacta_Arquivo] GO IF (EXISTS(SELECT NULL FROM sys.assemblies WHERE [name] = 'SQLCLR_Zip')) DROP ASSEMBLY [SQLCLR_Zip] CREATE ASSEMBLY [SQLCLR_Zip] FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300CD491C5B0000000000000000E00022200B013000001A0000000600000000000016390000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000C43800004F00000000400000B002000000000000000000000000000000000000006000000C0000008C3700001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000001C19000000200000001A000000020000000000000000000000000000200000602E72737263000000B00200000040000000040000001C0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000002000000000000000000000000000004000004200000000000000000000000000000000F83800000000000048000000020005001C2700007010000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B3005001B03000001000011000F03280500000A16320D0F03280500000A1F09FE022B01170D092C1100720100007028040000060038EC02000000000F00280600000A280700000A16FE01130411042C1C0072F4000070048C07000001280800000A280400000600DDB902000000DE1D2600726E010070048C07000001280800000A280400000600DD9902000000000F00280600000A0F01280600000A280900000A0A00DE1D260072CE010070048C07000001280800000A280400000600DD630200000F02280600000A730A00000A0B076F0B00000A252D0426142B056F0C00000A0C082C0B08280700000A16FE012B0117130511052C1700723C02007008280800000A2804000006003817020000076F0D00000A6F0E00000A72D9020070280F00000A130611062C2D0000076F1000000A0000DE1E260072DB0200700F02280600000A280800000A280400000600DDD2010000002B2100724F030070076F0D00000A6F0E00000A280800000A28040000060038AE010000000072E00300700F03280500000A8C140000010F02280600000A281100000A0F04281200000A2D0E0F04280600000A281300000A2C07720C0400702B1172100400700F04280600000A280800000A72200400700F00280600000A0F01280600000A281100000A281400000A1307731500000A256F1600000A723E0400706F1700000A00256F1600000A166F1800000A00256F1600000A11076F1900000A00256F1600000A176F1A00000A00256F1600000A176F1B00000A00256F1600000A2052030000281C00000A6F1D00000A00256F1600000A2052030000281C00000A6F1E00000A00256F1600000A176F1F00000A0013090011096F2000000A2611096F2100000A6F2200000A130A11096F2300000A6F2200000A130B110A6F2400000A16FE02130C110C2C020000110B6F2400000A16FE02130D110D2C0A00110B2804000006000000DE0D11092C0811096F2500000A00DC068E6913081A8D050000012516725E040070A2251711088C14000001A225187276040070A225190F02280600000AA2282600000A28050000060000DE1D130E0072BA040070110E6F2700000A282800000A28040000060000DE002A00417C0000000000002F00000035000000640000001D000000050000010000000082000000180000009A0000001D00000005000001000000001F0100000B0000002A0100001E00000005000001020000005F02000054000000B30200000D00000000000000000000006D01000090010000FD0200001D0000000B0000011B300400F701000002000011000F00280600000A730A00000A0A066F0B00000A2C15066F0B00000A6F0C00000A280700000A16FE012B01170B072C1C00723C020070066F0B00000A280800000A28040000060038AA010000066F2900000A16FE010C082C1D0072CA0400700F00280600000A280800000A2804000006003880010000000072180500700F00280600000A0F01280600000A0F02280600000A282A00000A0F03281200000A2D0E0F03280600000A281300000A2C0772D90200702B1172100400700F03280600000A280800000A724E050070281400000A0D731500000A256F1600000A723E0400706F1700000A00256F1600000A166F1800000A00256F1600000A096F1900000A00256F1600000A176F1A00000A00256F1600000A176F1B00000A00256F1600000A2052030000281C00000A6F1D00000A00256F1600000A2052030000281C00000A6F1E00000A00256F1600000A176F1F00000A0013040011046F2000000A2611046F2100000A6F2200000A130511046F2300000A6F2200000A130611056F2400000A16FE02130711072C0A0011052805000006000011066F2400000A16FE02130811082C0A0011062804000006000000DE0D11042C0811046F2500000A00DC725E0500700F01280600000A282800000A28050000060000DE1D13090072BA04007011096F2700000A282800000A28040000060000DE002A004134000002000000560100005C000000B20100000D00000000000000000000007700000062010000D90100001D0000000B0000012202282B00000A002A3E000228050000060002732C00000A7A0000001B30040080000000030000110072AA050070732D00000A0A00066F2E00000A0072DA05007006732F00000A0C00086F3000000AA51400000116FE030D092C02DE4A00DE0B082C07086F2500000A00DC283100000A0B072D022B2307026F2400000A20A00F00003003022B0C021620A00F00006F3200000A283300000A0000DE0B062C07066F2500000A00DC2A011C0000020020001838000B0000000002000C006874000B0000000013300200150000000400001100283100000A0A062D022B080602283400000A002A2A0203283500000A00002A42534A4201000100000000000C00000076342E302E33303331390000000005006C00000098030000237E000004040000E404000023537472696E677300000000E80800004806000023555300300F0000100000002347554944000000400F00003001000023426C6F620000000000000002000001471502000900000000FA013300160000010000001D00000004000000070000000D00000035000000050000000400000001000000030000000000A2010100000000000600CB00AD030600EB00AD030600A1009A030F00CD03000006003A04CE010A00B50049030A006801E5030A000100E5030600530213000E000B049A0306001302CE010A00FA0145040A008A0049030A004C0045040A00180345040600BD04130006007401CE0106008A02130006005C02130006000400CE010E0079029A0306005F018F0406003103130006003E03130006006800CE0106000802CE010A00ED011D020A0042001D020A009B044903000000000A00000000000100010001001000FA03000015000100010081011000AD02900115000100040001001000410490012D00010007005020000000009600C602D5000100F423000000009600DA02E30006002C26000000008618900306000A003526000000009600B502EF000A004826000000009600BC01EF000B00F0260000000096000A03F4000C001127000000008618900340000D0000000100980200000200BF02000003004102000004003002000005001D0000000100410200000200A00200000300BF02000004001D0000000100BA0200000100C501000001002603000001009603090090030100110090030600190090030A0031009003060041001D01260039001D012A00810025042E008900330433008100DC0339004900900340004900B9044500290072012A009900DF012A008900D5012A008900C7044A0099009A0006008900330450003900B10157008900D5042E0089002C045B0051009003060051006B026200A90074004000A90009016700A90013044000A90074046700A90076036700B10041016C00A9004D017200A90027017200A900A604670051005B045700510061047800C10038002A00510064037800890085012600C9009200060089002C047D0059005C002A0089002C048300990021045700890033049900290090030600D10090034000610090034000D900DA01060071009003AB00E100FC02B200E9008100B60089007B01BB0069005700400069005700C600590090034000200023002B012E000B00FA002E00130003012E001B002201400023002B0110008900A100C100048000000000000000000000000000000000F1020000040000000000000000000000CC002F0000000000040000000000000000000000CC00230000000000040000000000000000000000CC00CE010000000000000053716C496E743332003C4D6F64756C653E0053797374656D2E494F0073656E68610053797374656D2E44617461006D73636F726C69620052656164546F456E64004462436F6D6D616E640053716C436F6D6D616E640053656E64006765745F4D6573736167650049446973706F7361626C65007365745F46696C654E616D65006765745F506970650053716C5069706500446973706F73650044656C6574650044656275676761626C654174747269627574650053716C50726F63656475726541747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465007365745F5573655368656C6C45786563757465006765745F56616C7565007365745F5374616E646172644572726F72456E636F64696E6700476574456E636F64696E67007365745F5374616E646172644F7574707574456E636F64696E670053716C537472696E6700546F537472696E6700537562737472696E67006765745F4C656E677468004269626C696F74656361732E4D6F64656C0053514C434C525F5A69702E646C6C006765745F49734E756C6C004D656E736167656D006D656E736167656D0053797374656D005472696D004F70656E006765745F457874656E73696F6E004462436F6E6E656374696F6E0053716C436F6E6E656374696F6E004170706C69636174696F6E457863657074696F6E0053797374656D2E446174612E436F6D6D6F6E006E6976656C436F6D706163746163616F006172717569766F436F6D7061637461646F0046696C65496E666F0046696C6553797374656D496E666F006765745F5374617274496E666F0050726F636573735374617274496E666F004469726563746F7279496E666F0063616D696E686F00706173746144657374696E6F005265746F726E6F004572726F006572726F0066696C74726F00737470436F6D70616374615F4172717569766F00737470446573636F6D70616374615F4172717569766F0053514C434C525F5A697000457865637574655363616C6172005265746F726E615265616465720053716C4461746152656164657200646174615265616465720053747265616D5265616465720054657874526561646572004D6963726F736F66742E53716C5365727665722E536572766572006765745F5374616E646172644572726F72007365745F52656469726563745374616E646172644572726F72002E63746F72007374720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730047657446696C65730053797374656D2E446174612E53716C54797065730053746F72656450726F636564757265730050726F63657373007365745F417267756D656E7473006765745F45786973747300436F6E63617400466F726D6174004F626A656374005265740053797374656D2E446174612E53716C436C69656E74005374617274006765745F5374616E646172644F7574707574007365745F52656469726563745374616E646172644F75747075740053797374656D2E546578740053716C436F6E74657874007365745F4372656174654E6F57696E646F77006765745F4469726563746F7279006F705F496E657175616C6974790049734E756C6C4F72456D70747900000080F150006100720061006D006500740072006F00200069006E007600E1006C00690064006F0020006E006100200076006100720069006100760065006C0020006E006900760065006C0043006F006D0070006100630074006100630061006F002C00200075007300650020006400650020003000200061002000390020007000610072006100200063006F006D007000610063007400610072002C002000730065006E0064006F002000390020006F0020006E006900760065006C0020006D00610069007300200061006C0074006F0020002800350020007200650063006F006D0065006E006400610064006F0029002E0001794F002000630061006D0069006E0068006F002000650073007000650063006900660069006300610064006F00200027007B0030007D00270020006E00E3006F00200065007800690073007400650020006F0075002000650073007400E100200069006E00610063006500730073006900760065006C002E00015F4500720072006F00200061006F00200063006F006E007600650072007400650072002000630061006D0069006E0068006F002000650073007000650063006900660069006300610064006F002000280027007B0030007D00270029002E00016D4500720072006F00200061006F0020006C006900730074006100720020006100720071007500690076006F00730020006E006F002000630061006D0069006E0068006F002000650073007000650063006900660069006300610064006F00200028007B0030007D0029002E0000809B4F002000630061006D0069006E0068006F00200064006F0020006100720071007500690076006F00200063006F006D007000610063007400610064006F002000650073007000650063006900660069006300610064006F00200028007B0030007D00290020006E00E3006F00200065007800690073007400650020006F007500200069006E0061006300650073007300ED00760065006C002E00010100736F0020006100720071007500690076006F00200063006F006D007000610063007400610064006F002000650073007000650063006900660069006300610064006F00200028007B0030007D0029002000650073007400E1002000730065006E0064006F00200075007300610064006F002E0001808F4F0020006100720071007500690076006F00200063006F006D007000610063007400610064006F002000650073007000650063006900660069006300610064006F0020006E00E3006F00200070006F007300730075006900200075006D006100200065007800740065006E007300E3006F0020007600E1006C00690064006100200028007B0030007D0029002E00012B2000610020002D0074007A006900700020002D006D0078007B0030007D00200022007B0031007D00220001032000000F20002D0070007B0030007D002000011D22007B0030007D005C007B0031007D00220020002D006D006D007400011F43003A005C00420069006E006E005C0037007A0061002E00650078006500001754006F00740061006C002000640065003A00200028000043290020006100720071007500690076006F007300200066006F00720061006D00200063006F006D007000610063007400610064006F007300200065006D003A002000000F4500720072006F0020003A002000004D4F0020006100720071007500690076006F00200063006F006D007000610063007400610064006F00200027007B0030007D00270020006E00E3006F0020006500780069007300740065002E00013520007800200022007B0030007D00220020002D0061006F00610020002D006F0022007B0031007D00220020007B0032007D002000010F2D00720020002D006D006D007400014B4F00730020006100720071007500690076006F007300200066006F00720061006D00200064006500730063006F006D007000610063007400610064006F007300200065006D003A002000002F63006F006E007400650078007400200063006F006E006E0065006300740069006F006E003D007400720075006500006B490046002000280020002800350031003200200026002000400040004F005000540049004F004E005300290020003D002000350031003200200029002000730065006C0065006300740020003100200065006C00730065002000730065006C0065006300740020003000000000BBDC01828FDA2244B062F1B52FB142420004200101080320000105200101111115070F1D0E12250E020202020E0812290E0E0202122D032000080320000E040001020E0500020E0E1C0600021D0E0E0E042001010E0420001249050002020E0E0600030E0E1C1C032000020600030E0E0E0E04200012550420010102050001125908052001011259042000125D0500010E1D1C0500020E0E0E0F070A122502020E12290E0E0202122D0700040E0E1C1C1C09070412311235123902062002010E12310320001C04000012350520020E0808040701123505200101123D08B77A5C561934E0890D000501111D111D111D1121111D0B000401111D111D111D111D040001010E05000101123D0801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301080100070100000000040100000000000000CD491C5B00000000020000001C010000A8370000A81900005253445359AF33E5F349BF4E81A533ACB119414D01000000433A5C55736572735C646966696C5C446F63756D656E74735C56697375616C2053747564696F20323031375C50726F6A656374735C53514C434C525F5A69705C53514C434C525F5A69705C6F626A5C44656275675C53514C434C525F5A69702E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000EC380000000000000000000006390000002000000000000000000000000000000000000000000000F8380000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000540200000000000000000000540234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004B4010000010053007400720069006E006700460069006C00650049006E0066006F0000009001000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E00300000003E000F00010049006E007400650072006E0061006C004E0061006D0065000000530051004C0043004C0052005F005A00690070002E0064006C006C00000000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000046000F0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C0043004C0052005F005A00690070002E0064006C006C0000000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000183900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 WITH PERMISSION_SET = UNSAFE GO CREATE PROCEDURE [dbo].[stpCompacta_Arquivo] @caminho [nvarchar](max), @filtro [nvarchar](max), @arquivoCompactado [nvarchar](max), @nivelCompactacao [int], @senha [nvarchar](max) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SQLCLR_Zip].[StoredProcedures].[stpCompacta_Arquivo] GO CREATE PROCEDURE [dbo].[stpDescompacta_Arquivo] @arquivoCompactado [nvarchar](max), @pastaDestino [nvarchar](max), @filtro [nvarchar](max), @senha [nvarchar](max) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SQLCLR_Zip].[StoredProcedures].[stpDescompacta_Arquivo] GO |
And if you want to download the Visual Studio 2017 project, to edit the Stored procedures as needed, click here on this link.
How to compress files and directories
| | -- Modo básico EXEC dbo.stpCompacta_Arquivo @caminho = 'C:\Temporario', @filtro = '*.txt', @arquivoCompactado = 'C:\Teste\Arquivo.zip', @nivelCompactacao = 5, @senha = '' |
| | -- Compactando todos os arquivos que comecem com Teste% EXEC dbo.stpCompacta_Arquivo @caminho = 'C:\Temporario', @filtro = 'Teste*', @arquivoCompactado = 'C:\Teste\Arquivo.zip', @nivelCompactacao = 5, @senha = '' |
| | -- Compactando o arquivo "Teste.txt" EXEC dbo.stpCompacta_Arquivo @caminho = 'C:\Temporario', @filtro = 'Teste.txt', @arquivoCompactado = 'C:\Teste\Arquivo.zip', @nivelCompactacao = 5, @senha = '' |
| | /* 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', @filtro = '*.txt', @arquivoCompactado = 'C:\Teste\Arquivo.zip', @nivelCompactacao = 9, @senha = 'dirceu' |
How to unzip files and directories
| | -- Exemplo básico EXEC dbo.stpDescompacta_Arquivo @arquivoCompactado = N'C:\Teste\Arquivo.zip', -- nvarchar(max) @pastaDestino = N'C:\Teste\', -- nvarchar(max) @filtro = N'*', -- nvarchar(max) @senha = N' ' -- nvarchar(max) |
| | -- Descompactando arquivos com senha EXEC dbo.stpDescompacta_Arquivo @arquivoCompactado = N'C:\Teste\Arquivo.zip', -- nvarchar(max) @pastaDestino = N'C:\Teste\', -- nvarchar(max) @filtro = N'*', -- nvarchar(max) @senha = N'dirceu' -- nvarchar(max) |
| | -- Descompactando apenas arquivos com extensão .txt EXEC dbo.stpDescompacta_Arquivo @arquivoCompactado = N'C:\Teste\Arquivo.zip', -- nvarchar(max) @pastaDestino = N'C:\Teste\', -- nvarchar(max) @filtro = N'*.txt', -- nvarchar(max) @senha = N'' -- nvarchar(max) |
| | -- Descompactando apenas o arquivo "Teste.txt" EXEC dbo.stpDescompacta_Arquivo @arquivoCompactado = N'C:\Teste\Arquivo.zip', -- nvarchar(max) @pastaDestino = N'C:\Teste\', -- nvarchar(max) @filtro = N'Teste.txt', -- nvarchar(max) @senha = N'' -- nvarchar(max) |
Note: I would like to point out that I identified a problem during testing with the stpDescompacta_SQLCLR File, which is trying to unzip a zip file that has a password, without specifying the password at the time of calling the SP, causing the 7za.exe process to hang. stopped waiting for user interaction. If this happens, neither will you cancel the process, and the process will terminate, just killing the 7za.exe process by the Windows task manager, which will cause the waiting session to continue processing.
In case you get the password wrong, SP will process normally and display the error message stating that the password is wrong.
To finalize the alternatives of this post, I would like to share a solution using lib
SharpZipLib, which allows you to compress and unzip files using only C # code, without relying on external binaries as in the previous examples (7za.exe).
If you do not know or do not know what is the SQLCLR, learn more by accessing the post Introduction to SQL Common Language Runtime (CLR) in SQL Server.
Because the SharpZipLib library is so large, it becomes impossible to make all the source code available here in the post, as well as the T-SQL version. For this reason, I will provide links to download the source files.
The complete Visual Studio solution where you can freely edit the code is available at this link.
This link here I am making available the T-SQL source code, to allow the creation of objects without having to install or open Visual Studio.
To better understand the @filter parameter of this solution, as it is different from previous alternatives (using 7-zip), I recommend reading the official library documentation.
How to compress files and directories
| | -- Exemplo básico EXEC dbo.stpCompacta_Arquivo @caminho = 'C:\Temporario\', @filtro = '*', @arquivoCompactado = 'C:\Teste\Arquivo.zip', @nivelCompactacao = 5, @senha = '' |
| | -- Compactando arquivos com extensão .txt e utilizando compactação máxima (9) EXEC dbo.stpCompacta_Arquivo @caminho = 'C:\Temporario\', @filtro = '*', @arquivoCompactado = 'C:\Teste\Arquivo.zip', @nivelCompactacao = 9, @senha = '' |
| | -- Compactando arquivos e protegendo-os com senha EXEC dbo.stpCompacta_Arquivo @caminho = 'C:\Temporario\', @filtro = '*', @arquivoCompactado = 'C:\Teste\Arquivo.zip', @nivelCompactacao = 5, @senha = 'dirceu' |
| | -- Compactando apenas o arquivo "Teste 1.txt" EXEC dbo.stpCompacta_Arquivo @caminho = 'C:\Temporario\', @filtro = 'Teste 1.txt', @arquivoCompactado = 'C:\Teste\Arquivo.zip', @nivelCompactacao = 5, @senha = '' |
How to unzip files and directories
| | -- Exemplo básico EXEC dbo.stpDescompacta_Arquivo @arquivoCompactado = N'C:\Teste\Arquivo.zip', -- nvarchar(max) @pastaDestino = N'C:\Teste\', -- nvarchar(max) @filtro = N'', -- nvarchar(max) @senha = N'' -- nvarchar(max) |
| | -- Descompactando arquivos com senha EXEC dbo.stpDescompacta_Arquivo @arquivoCompactado = N'C:\Teste\Arquivo.zip', -- nvarchar(max) @pastaDestino = N'C:\Teste\', -- nvarchar(max) @filtro = N'', -- nvarchar(max) @senha = N'dirceu' -- nvarchar(max) |
| | -- Descompactando apenas arquivos com extensão .txt EXEC dbo.stpDescompacta_Arquivo @arquivoCompactado = N'C:\Teste\Arquivo.zip', -- nvarchar(max) @pastaDestino = N'C:\Teste\', -- nvarchar(max) @filtro = N'.txt', -- nvarchar(max) @senha = N'' -- nvarchar(max) |
| | -- Descompactando arquivos que começam com "Teste%" EXEC dbo.stpDescompacta_Arquivo @arquivoCompactado = N'C:\Teste\Arquivo.zip', -- nvarchar(max) @pastaDestino = N'C:\Teste\', -- nvarchar(max) @filtro = N'Teste*', -- nvarchar(max) @senha = N'' -- nvarchar(max) |
Good afternoon, how are you? Great explanation, congratulations. I did the procedure that passed me, but when I run the sp it runs, but it appears: WARNING: Access is denied. So I can't zip the content, it creates the empty folder.
I'm logged in to the bank as admin, I have permissions on the folder, what can it be?