SQL Server - How to compress and unzip files and directories using 7-zip and xp_cmdshell or SQLCLR (C #)

Views: 2.420
Reading Time: 10 minutes

Hey guys!

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).

If you are interested in handling files with SQL Server, here are some more posts on this subject:
- SQL Server - File Operations Using xp_cmdshell (Listing, Reading, Writing, Copying, Deleting, and Moving Files)
- How to compress directories in ZIP archives using 7zip and CMD
- SQL Server - How to export database data to text file (CLR, OLE, BCP)
- SQL Server - How to import text files into database (OLE Automation, CLR, BCP, BULK INSERT, OPENROWSET)
- SQL Server - How to list, read, write, copy, delete and move files with CLR (C #)
- File Operations Using OLE Automation in SQL Server
- SQL Server - How to export and import tabular data files (Ex: CSV) using CLR (C #)
- Importing CSV Files into the SQL Server Database
- SQL Server - How to integrate with FTP and list, upload, and download files using CLR (C #)

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.

Alternative #1: Using 7-zip and xp_cmdshell

View content
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:

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.

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):

Result:

ZIP file generated:

Another examples:

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.

Examples of use:

#2 Alternative: Using 7-zip and SQLCLR (C #)

View content
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)

stpCompacta_Archive.cs

stpDescompact_Archive.cs

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)

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

How to unzip files and directories

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.

#3 Alternative: Using SharpZipLib and SQLCLR (C #)

View content
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

How to unzip files and directories

That's it folks!
Hope you enjoyed this post, a hug and see you next time!