script to compress files one by one RRS feed

  • Question

  • Hello people

    We have SQL Server 2016 with audit trails enabled, generating files like this:



    Each one with 1 GB, being that at the end of the day I have around 20 files of these of 1 Gb each.

    I need a routine to compress these files, so I can send them to the cloud, already tried with 7-zip, and winrar but it did not work.

    I need compression to keep the files name and not everything in a single file

    Can anyone help me with this?

    Tuesday, June 18, 2019 5:02 PM

All replies

  • Try WinZip. It seems to not have issues with large files.

    Your question has noting to do with scripting so you should post in another forum or contact a consultant to help you solve this issue.


    Tuesday, June 18, 2019 5:30 PM
  • I've got a couple of powershell scripts that may help you.

    This is a function that if you pass it the parameters of a folder you want to watch as the source and a destination of where you want the zip file. It will loop through all folders in the watched folder and zip them with a date stamp at the front and delete the original folder. You need 7-zip installed


    function CompressFunction {
        param ($filePath,$destPath)
    	#Checks for any running 7zip processes on the local machine and assigns them to the variable
        $7zip = Get-Process 7z -ErrorAction SilentlyContinue
    	#If there are running 7-zip processes running assigned to this variable then stop the script
        if ($7zip){
            Write-Host "7-zip already running" 
        #Checks to see if the default 7-zip program path exists and if it doesn't then warn it needs installing
        if (-not (Test-Path "$env:ProgramFiles\7-Zip\7z.exe")){
        Write-Host "$env:ProgramFiles\7-Zip\7z.exe needed"
            #If 7-zip default program path does exist then carry on with the rest of script
            else {
                Write-Host "$env:ProgramFiles\7-Zip\7z.exe found."
    			#Load the 7-zip executable into the variable
                $cmd = ("$env:ProgramFiles\7-Zip\7z.exe")
                # get directories only (without -Recursive)
                $folders = Get-ChildItem -Path $filePath -Directory | Where-Object {$_.mode -match 'd'}
                $i = 0
    				#For each subfolder in folders set the 7-zip paramater variables and then execute the 7-zip command variable
                    foreach ($subfolder in $folders) {
                    $timestamp = get-date -f dd.MM.yy
                    $source    = $subfolder.fullname
                    $target    = "$destPath\$timestamp`_$($"
                    # -mx1 "copy" mode -mx5 "normal" -mx9 "ultra compression"
                    & $cmd "a" "-mx=9" "$target" "$source" "-t7z"
                    Write-Output "$i. $filePath\$timestamp-$($"
    				#Once completed compression then delete the original folder
                    Remove-Item -Path $subfolder.fullname -Force -Recurse
            Write-Host "$i directories zipped in $filePath"

    An example of how to call it and pass parameters below. The calling script is assuming that the 2 scripts are in the same directory as eachother.

    Calling Script

    . $PSScriptRoot\Compress-Function.ps1
    $filePath = (Get-Item -Path "\\path\to\folder\to\watch" -Verbose).FullName
    $destPath = "\\path\to\destination\folder"
    CompressFunction -filePath $filePath -destPath $destPath

    You can then setup the calling script to be run in task scheduler for automation.

    Hope this is helpful.

    Tuesday, June 25, 2019 4:17 PM