Batch Compress SQL Backups and upload to FTP

SQL backups generally have a very good compression ratio. Storing these at an offsite location would be the first step in a disaster recovery plan. The script below will compress all files with a .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%
)

---

_{Originally posted to: http://www.thesysadminhimself.com/2011/03/batch-compress-sql-backups-and-upload.html}