Jignesh Raiyani

Prepare ZIP or RAR files in SQL Server Using xp_cmdshell (T-SQL)

October 21, 2019 by

Microsoft SQL Server offers the ability to export data into CSV, Excel, PDF and other formats with the help of xp_cmdshell in T-SQL.

While typically the size of the file depends on the amount of data that is exported, when furnishing to domain-specific, the size of the data export will be larger in size than normal. This requires more server-side resources, such as CPU, memory, file system, etc., and may cause issues on the end-user side. If the file size is much larger than needed, it will take longer to download. While download time depends on the end user’s internet bandwidth, it is much more dependent on the server side’s configured resources:

Compress file using SQL Server(TSQL)

The prevalent way to store and share sets of multiple files is to use the ZIP or RAR file format. The ZIP or RAR command compresses one or more files and bundles them into a single file. The most modern operating systems, including Microsoft Windows, MAC OS, and Unix/Linux, can handle ZIP files. If you use a Linux operating system with SQL Server, you may want to use the Unix zip command-line tool to manage these files.

If bandwidth is limited, creating a ZIP or RAR file will allow faster downloads and will reduce the chance of data corruption. In this article, we’ll show you how to use T-SQL fundamentals to compress files to achieve various output requirements using xp_cmdshell (T-SQL) in SQL Server.

xp_cmdshell

A generated Windows command shell is passed in a string for execution by SQL Server. Looking at it closely, we see that it is an extended stored procedure, provided by Microsoft and stored in the master database. This stored procedure allows you to pass the operating system commands directly to the Windows command shell with the help of T-SQL code.

In this case, the output of this command must be returned to the calling routine as rows of text. SQL Server can block access to procedure ‘sys.xp_cmdshell‘ by component ‘xp_cmdshell‘ because this component is turned off as part of the security configuration. A database administrator or system administrator can enable the use of ‘xp_cmdshell‘ by using sp_configure in SQL Server.

Enable xp_cmdshell SQL Server

Enable xp_cmdshell using sp_configure

Using the above T-SQL command, we can enable the xp_cmdshell to use further Windows commands inside SQL Server. If a user does not have adequate permission to execute the above code, then it will return an error: “User does not have permission to perform this action”. If the user does have permission to perform this action, it will return an affirmative response.

Compress file or group of files with ZIP or RAR

You can use a stored procedure to create the zip files; however, you would be writing T-SQL which writes files to disk and executes Windows system commands as well. You could read up on xp_cmdshell, but you’ll still have a large zip file coming back to your server in that model. You may ask, couldn’t the users still overload your system? Yes, but you can get around this issue by using streaming, which can be done with the zip files you create.

Windows does not come with a command-line zip program, so I recommend the zip utility, WinRAR, which includes a command-line executable and supports many various archive file types. You can also use other open-source Windows utilities in a similar manner:

  • Download WinRAR
  • Open a CMD using SQL Server xp_cmdshell and use different parameters to compress the files

Add file to archive using xp_cmdshell

When adding a file to the archive, WinRAR supports the “a” parameter within the command statement. The command can be segregated into four distinctive parts, Utility Path, WinRAR parameter, Destination Address, and Source Address:

In actual scenarios, files are generated from SQL Server with a reformulated location and unique filename. SQL Server compresses the files into ZIP or RAR formats using a function or procedure. The T-SQL statement shown below will archive the file:

My Source File path is: V:\FileCompress\Source Directory\1109_2019-09-23 155716.140.csv

Expected Destination File: V:\FileCompress\Destination Directory\1109_2019-09-23 155716.140.RAR

Compressed file using xp_cmdshell Here, the “a” is a parameter to add a file for Archive. As a result, the file is successfully archived with the RAR extension with just a 31 KB size.

Compressed file Directory in file system

Now, the problem is that the archived file has been placed in the RAR Directory as a Source location (FileCompress\Source Directory). Therefore, “-ep1” needs to be added with “a” to avoid use of the RAR base directory, as shown in the T-SQL statement below:

EXEC master.dbo.xp_cmdshell ‘””C:\Program Files\WinRAR\Rar.exe” a -ep1 “V:\FileCompress\Destination Directory\1109_2019-09-23 155716.140.RAR” “V:\FileCompress\Source Directory\1109_2019-09-23 155716.140.csv””‘

Compressed file Directory withoud folder tree in file system We can check in below snap, the file is directly populated in the base, and not within the folder:

Archive multiple files into single ZIP or RAR

To use the ZIP or RAR commands for multiple files, we can include as many filenames as needed in arguments within the command line. We can include multiple files to single compressed ZIP or RAR format in SQL Server with xp_cmdshell:

Syntax:

For example, we have 2 files 1109_2019-09-23 155716.141.csv & 1109_2019-09-23 155716.140.csv on directory address “V:\FileCompress\Source Directory\” and want to compress them to file 1109_2019-09-23 155716.140.RAR in directory “V:\FileCompress\Destination Directory”. We would use this command:

Use double quotes to avoid space issue in source and destination directories

Any of these utilities will return an error when there is a space character in the source or destination folder name or file name. Using “” (double quotes) will avoid the error. Above, we have “” around both the source (“V:\FileCompress\Source Directory\1109_2019-09-23 155716.140.csv”) and destination files (“V:\FileCompress\Destination Directory\1109_2019-09-23 155716.140.RAR”).

WinRAR Parameters

Different utilities have different command-line parameters to invoke various features. For WinRAR, we have a list of parameters below that can greatly expand the functionality of the utility. You can generate a list of these using either of the following commands:

C:\Program Files\WinRAR>RAR /?

C:\Program Files\WinRAR>RAR –help

Refer to the list below for the commands and related switches for the WinRAR utility:

Commands:

a

Add files to archive

c

Add archive comment

ch

Change archive parameters

cw

Write archive comment to file

d

Delete files from archive

e

Extract files without archived paths

f

Freshen files in archive

i[par]=<str>

Find string in archives

k

Lock archive

l[t[a],b]

List archive contents [technical[all], bare]

m[f]

Move to archive [files only]

p

Print file to stdout

r

Repair archive

rc

Reconstruct missing volumes

rn

Rename archived files

rr[N]

Add data recovery record

rv[N]

Create recovery volumes

s[name|-]

Convert archive to or from SFX

t

Test archive files

u

Update files in archive

v[t[a],b]

Verbosely list archive contents [technical[all],bare]

x

Extract files with full path

Switches:

Stop switches scanning

@[+]

Disable [enable] file lists

ac

Clear archive attribute after compression or extraction

ad

Append archive name to destination path

ag[format]

Generate archive name using the current date

ai

Ignore file attributes

ao

Add files with archive attribute set

ap<path>

Set path inside archive

as

Synchronize archive contents

c-

Disable comments show

cfg-

Disable read configuration

cl

Convert names to lower case

cu

Convert names to upper case

df

Delete files after archiving

dh

Open shared files

dr

Delete files to Recycle Bin

ds

Disable name sort for solid archive

dw

Wipe files after archiving

e[+]<attr>

Set file exclude and include attributes

ed

Do not add empty directories

en

Do not put ‘end of archive’ block

ep

Exclude paths from names

ep1

Exclude base directory from names

ep2

Expand paths to full

ep3

Expand paths to full including the drive letter

f

Freshen files

hp[password]

Encrypt both file data and headers

ht[b|c]

Select hash type [BLAKE2,CRC32] for file checksum

id[c,d,p,q]

Disable messages

ieml[addr]

Send archive by email

ierr

Send all messages to stderr

ilog[name]

Log errors to file (registered versions only)

inul

Disable all messages

ioff

Turn PC off after completing an operation

isnd

Enable sound

iver

Display the version number

k

Lock archive

kb

Keep broken extracted files

log[f][=name]

Write names to log file

m<0..5>

Set compression level (0-store…3-default…5-maximal)

ma[4|5]

Specify a version of archiving format

mc<par>

Set advanced compression parameters

md<n>[k,m,g]

Dictionary size in KB, MB or GB

ms[ext;ext]

Specify file types to store

mt<threads>

Set the number of threads

n<file>

Additionally filter included files

n@

Read additional filter masks from stdin

n@<list>

Read additional filter masks from list file

o[+|-]

Set the overwrite mode

oc

Set NTFS Compressed attribute

oh

Save hard links as the link instead of the file

oi[0-4][:min]

Save identical files as references

ol[a]

Process symbolic links as the link [absolute paths]

oni

Allow potentially incompatible names

or

Rename files automatically

os

Save NTFS streams

ow

Save or restore file owner and group

p[password]

Set password

p-

Do not query password

qo[-|+]

Add quick open information [none|force]

r

Recurse subdirectories

r-

Disable recursion

r0

Recurse subdirectories for wildcard names only

ri<P>[:<S>]

Set priority (0-default,1-min..15-max) and sleep time in ms

rr[N]

Add data recovery record

rv[N]

Create recovery volumes

s[<N>,v[-],e]

Create solid archive

s-

Disable solid archiving

sc<chr>[obj]

Specify the character set

sfx[name]

Create SFX archive

si[name]

Read data from standard input (stdin)

sl<size>

Process files with size less than specified

sm<size>

Process files with size more than specified

t

Test files after archiving

ta<date>

Process files modified after <date> in YYYYMMDDHHMMSS format

tb<date>

Process files modified before <date> in YYYYMMDDHHMMSS format

tk

Keep original archive time

tl

Set archive time to latest file

tn<time>

Process files newer than <time>

to<time>

Process files older than <time>

ts[m|c|a]

Save or restore file time (modification, creation, access)

u

Update files

v<size>[k,b]

Create volumes with size=<size>*1000 [*1024, *1]

vd

Erase disk contents before creating volume

ver[n]

File version control

vn

Use the old style volume naming scheme

vp

Pause before each volume

w<path>

Assign work directory

x<file>

Exclude specified file

x@

Read file names to exclude from stdin

x@<list>

Exclude files listed in specified list file

y

Assume Yes on all queries

z[file]

Read archive comment from file

Conclusion

Client-specific requirements or bandwidth optimization will determine whether you use file compression. If you do need to compress files within SQL Server, we’ve shown you how to write a simple program using the xp_cmdshell command in T-SQL that can be used with a number of parameters.

Jignesh Raiyani

Jignesh Raiyani

Jignesh has good experience in Database Solutions and Architecture, working with multiple customers on Database Design & Architecture, SQL Development, Administration, Query Optimization, Performance Tuning, HA and Disaster Recovery.

View all posts by Jignesh Raiyani
Jignesh Raiyani
309 Views