Script to 7-zip all files and then delete the originals

22 Nov

I wrote a while back about a script which would go through my backup directory and 7-zip my .bak and .sql files and then delete the originals, so that they were much smaller to then backup to my elephant drive.

Rather than point out all the individual changes, here’s the complete new script to work with 7-Zip (which is free btw!):

(compress.vbs)

‘Basically, go through the directory below, find all .sql and .bak files
‘and create a 7z file from the original
‘then delete the .bak (MYQSL) and .sql (MSSQL) file, simples!
‘dave harris – dave@harris.net 21/11/2010

Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Const ForAppending = 2
Dim objFSO:Set objFSO = CreateObject(“Scripting.FileSystemObject”)

LogFile = “d:\scripts\compressanddelete\compressanddelete.log”
Dim objLogFile:Set objLogFile = objFSO.CreateTextFile(logfile, 2, True)

objStartFolder = “d:\backup”

Dim fso
Set fso = CreateObject(“Scripting.FileSystemObject”)

Set objShell = CreateObject(“WScript.Shell”)

Set objFolder = objFSO.GetFolder(objStartFolder)
objLogFile.Write objFolder.Path
objLogFile.Writeline
Set colFiles = objFolder.Files
For Each objFile in colFiles
    objLogFile.Write objFile.Name
    objLogFile.Writeline
Next

ShowSubfolders objFSO.GetFolder(objStartFolder)

Sub ShowSubFolders(Folder)
    For Each Subfolder in Folder.SubFolders
        objLogFile.Write Subfolder.Path
        objLogFile.Writeline
        Set objFolder = objFSO.GetFolder(Subfolder.Path)
        Set colFiles = objFolder.Files
        For Each objFile in colFiles
            objLogFile.Write objFile.Name
            objLogFile.Writeline

        if ((right(objFile.Name,3)=”sql”) or (right(objFile.Name,3)=”BAK”)) then
        objShell.Run “c:\progra~1\7-zip\7z.exe a -v1073741824 -wd:\temp -mx9 -mmt ” & Subfolder.Path & “\” & objFile.Name & “.7z ” & Subfolder.Path & “\” & objFile.Name, 1, true
            fso.deletefile Subfolder.Path & “\” & objFile.Name, true
        else
            ‘msgbox(“already 7z”)
        end if

        Next
        ShowSubFolders Subfolder
    Next
End Sub

objLogFile.Close

It’s pretty simple stuff. It searches every file in d:\backup and checks if it’s a .7z file. If not, then it creates a .7z archive from the file (and names it filename.ext.7z), and then deletes the original file. I also pass in the following parameters to optimise it:

•  -mx9 = Use ultra compression
•  -mmt = Use multithreading
•  -w = Set working directory to a different drive
•  -v = Set volume size (split)

Set this to run as a scheduled task and boom, lots of space saved.

Update: For Full SQL Backups, you may want to check for transaction logs too:

        if ((right(objFile.Name,3)=”sql”) or (right(objFile.Name,3)=”trn”) or (right(objFile.Name,3)=”BAK”)) then