flyfox

將 SQL bak 每天壓縮、備份至 FTP。

@echo on
@setLocal EnableDelayedExpansion

@rem =========================================
@rem Author： flyfox 20140101
@rem 使用需知
@rem 1. 需安裝 7-zip
@rem =========================================

@rem =========================================
@rem 設定各路徑變數
@rem =========================================
@set file=BE_%date:~0,4%%date:~5,2%%date:~8,2%.exe
@set SDir=E:\Backup\SQLServer
@set DDir=E:\Backup\SQLServer
@set FtpData=D:\bat\ftp.txt
@set zz="C:\Program Files\7-Zip\7z.exe"
@set md=D:\bat\md5.exe
@set ziplog=D:\bat\log

@rem =========================================
@rem 設定備份檔名稱、log檔名稱
@rem =========================================
@set log=D:\bat\log\log-%date:~0,4%%date:~5,2%%date:~8,2%-%time:~0,2%%time:~3,2%%time:~6,2%.txt
@set log=%log: =0%
@set check=md5-%date:~0,4%%date:~5,2%%date:~8,2%.txt
@chdir /d E:\Backup\SQLServer > %log%
@dir /B | findstr %date:~0,4%%date:~5,2%%date:~8,2%0100 > file.txt
@echo findstr success >> %log%
@for /F %%i in (E:\Backup\SQLServer\file.txt) do @echo %%i >> %log%
@for /F %%i in (E:\Backup\SQLServer\file.txt) do (
set /A num=!num!+1
set var!num!=%%i
) >> %log%

:compress
@rem =========================================
@rem 設定壓縮 DB
@rem =========================================
@echo ========================================= >> %log%
@echo Date: %date% Time: %time% >> %log%
@if exist %DDir%\%file% (del /F /S %DDir%\%file%)
@%zz% a -mmt=4 -mx=9 -sfx %DDir%\%file% -pabc123 -mhe %var1% %var2% %var3% %var4% %var5% %var6% %var7% %var8% %var9% %var10% %var11% %var12% %var13% && echo. >> %log%
@if exist %DDir%\%file% (echo compress success! >> %log%) else (goto closezip)
@echo running ftp.txt && echo. >> %log%

:ftp
@rem =========================================
@rem 設定 FTP 上傳文字檔
@rem =========================================
@echo open 192.168.0.1> %FtpData%
@echo dbbackup>> %FtpData%
@echo dbbackup>> %FtpData%
@echo bin>> %FtpData%
@echo lcd %DDir%>> %FtpData%
@echo cd Backend>> %FtpData%
@echo del %file%>> %FtpData%
@echo put %file%>> %FtpData%
@echo by>> %FtpData%
@echo ========================================= >> %log%
@echo Date: %date% Time: %time% >> %log%
@echo running ftp >> %log%
@ftp -s:%FtpData% >> %log%
@echo FTP Success! >> %log%
@echo ========================================= >> %log%
@echo Date: %date% Time: %time% >> %log%
@forfiles /P %SDir% /S /M *.bak /D -3 /C "cmd /c del @path"
@forfiles /P %ziplog% /S /M *.* /D -30 /C "cmd /c del @path"
@del /F /S %FtpData% >> %log%
@del /F /S %DDir%\%file% >> %log%
@del /F /S %DDir%\file.txt >> %log%
@echo on
@exit

:closezip
@rem =========================================
@rem DB 備份檔壓縮失敗
@rem =========================================
@echo compress fail! >> %log%
@forfiles /P closezip /S /M *.* /D -30 /C "cmd /c del @path"
@exit