Batch Compress SQL Backups and upload to FTP

I always repeat myself saying that compressing SQL backups is a really-really good thing. Storing backups at an offsite location is another good thing. Combining the two would be the first step in a disaster recovery plan. That's why I created a little script that will compress all files with .bak extension and upload them to an FTP server if needed. I added some variables for easier customization.

@echo off

::::::::::::::::::::::::::::::::
:: compress SQL backups v1.02 ::
::::::::::::::::::::::::::::::::

:: what's new
:: v1.02 - added ERRORLEVEL handling when forfiles find no files matching criteria
:: v1.01 - added BackupDeleteOlderThanDays function
:: v1.02 - fixing ERRORLEVEL

:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: Variables:                                                                                                              ::
:: BACKUPDIRECTORY - the directory where the .bak files are located                                                        ::
:: BACKUPDELETEOLDERTHANDAYS - delete backup files older than x days. If no value specified, backups will not be deleted   ::
:: PACKLOWPRIORITY - use 1 to set 7-zip start in low priority mode. recommended.                                           ::
:: PACKONECPU - use 1 to set 7-zip use only one CPU for compression. recommended for servers with constantly high CPU load ::
:: PACKCOMPRESSION - use MAX or FAST - max will use LZMA2 Ultra compression while FAST will do PPMD Normal                 ::
:: UPLOADTOFTP and FTP* are self-explanatory                                                                               ::
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

set BackupDirectory=D:\SQLBackups\Scheduled
set BackupDeleteOlderThanDays=90
set PackLowPriority=1
set PackOneCPU=1
set PackCompression=MAX
set UploadToFTP=1
set FTPHost=10.192.32.11
set FTPUser=username
set FTPPass=password
set FTPDir=SQLBackups\Production

if %PackLowPriority% == 1 ( set PackLowPriority=/low ) ELSE ( set PackLowPriority=/normal )
if %PackOneCPU% == 1 ( set PackOneCPU=-mmt=off ) ELSE ( set PackOneCPU= ) 
if %PackCompression% == MAX ( set PackCompression=-mx9 -t7z -m0=lzma2 ) ELSE ( set PackCompression=-mx5 -t7z -m0=PPMd )
cd /d %BackupDirectory%

if %UploadToFTP% == 1 (
 echo user %FTPUser%> %temp%\daily_ftp.txt
 echo %FTPPass%>> %temp%\daily_ftp.txt
 echo cd %FTPDir%>> %temp%\daily_ftp.txt
 echo bin>> %temp%\daily_ftp.txt
)

FOR /r %%F IN (*.bak) DO (
 start "Pack" /w %PackLowPriority% "c:\Program Files\7-Zip\7z.exe" u %PackCompression% "%%F.7z" "%%F"
 if exist %%F.7z (
  IF !ERRORLEVEL! == 0 (
   del "%%F"
   if %UploadToFTP% == 1 ( echo put %%F.7z>> %temp%\daily_ftp.txt )
   )
 )
)

if %UploadToFTP == 1 (
 echo bye>> %temp%\daily_ftp.txt
 ftp -n -s:%temp%\daily_ftp.txt %FTPUser%
 del %temp%\daily_ftp.txt
 set FTPHost=
 set FTPUser=
 set FTPPass=
 set FTPDir=
)

if defined BackupDeleteOlderThanDays (
 set ErrorLevelOriginal=!ERRORLEVEL!
 Forfiles -p %BackupDirectory% -s -m *.bak -d -%BackupDeleteOlderThanDays% -c "cmd /c del /q @path" 
 Forfiles -p %BackupDirectory% -s -m *.zip -d -%BackupDeleteOlderThanDays% -c "cmd /c del /q @path" 
 Forfiles -p %BackupDirectory% -s -m *.rar -d -%BackupDeleteOlderThanDays% -c "cmd /c del /q @path" 
 Forfiles -p %BackupDirectory% -s -m *.7z -d -%BackupDeleteOlderThanDays% -c "cmd /c del /q @path" 
 set ERRORLEVEL=%ErrorLevelOriginal%
)

NOTE: To copy the entire code, just double-click and hit CTRL+C
NOTE: There are some issues with the syntax highlighter, some comment lines are colored red instead of green.