Prepare ZIP or RAR files in SQL Server Using xp cmdshell T SQL
Prepare ZIP or RAR files in SQL Server Using xp_cmdshell T-SQL 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 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[+] 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[bc] 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[45] Specify a version of archiving format mc Set advanced compression parameters md[k,m,g] Dictionary size in KB, MB or GB ms[ext;ext] Specify file types to store mt Set the number of threads n Additionally filter included files n@ Read additional filter masks from stdin n@
SQLShack
SQL Server training EspañolPrepare ZIP or RAR files in SQL Server Using xp_cmdshell T-SQL
October 21, 2019 by Jignesh Raiyani 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: 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
123456789101112131415 -- Show Advanced OptionsEXEC sp_configure 'show advanced options', 1GORECONFIGUREGO-- Enable xp_cmdshellEXEC sp_configure 'xp_cmdshell', 1GORECONFIGUREGO-- Hide Advanced OptionsEXEC sp_configure 'show advanced options', 0GORECONFIGUREGO 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 filesAdd 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: 1 EXEC master.dbo.xp_cmdshell 'cmd ""Utility Path" "Utility Parameter" "Destination Address" "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 1 EXEC master.dbo.xp_cmdshell '""C:\Program Files\WinRAR\Rar.exe" a "V:\FileCompress\Destination Directory\1109_2019-09-23 155716.140.RAR" "V:\FileCompress\Source Directory\1109_2019-09-23 155716.140.csv""' 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. 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””‘ 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: 1 EXEC master.dbo.xp_cmdshell 'cmd ""Utility Path" "Utility Parameter" "Destination Address" "Source File-1" "Source File-2" "Source File-3"..."Source File-n""' 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: 1 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" "V:\FileCompress\Source Directory\1109_2019-09-23 155716.141.csv""'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]=- 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 [noneforce] r Recurse subdirectories r- Disable recursion r0 Recurse subdirectories for wildcard names only ri
[:] 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[