通过批处理，实现SQL Server数据库定期备份到ftp

@echo off
rem --------------------- 配置: ftp上传参数,及本地文件目录(数据库备份的临时目录)-----------------
set "PUT_FTP_SERVER=192.168.1.100"
set "PUT_FTP_PORT=21"
set "PUT_FTP_USERNAME=ftpuser"
set "PUT_FTP_PASSWORD=ftppasswd"
set "PUT_DIR=/"
set "LOCAL_DIR=D:\sql_dbbackup\"
rem ----------------------程序开始-----------------------------------------------------------------------
echo begin_backup_db
rem 调用数据库备份存储过程,参数为本地目录. 数据库返回"过期需要删除的备份文件"
sqlcmd -E -S 127.0.0.1,1333 -d master -v BAK_PATH="'%LOCAL_DIR%'" -Q "execute dbo.p_job_backup $(BAK_PATH);" -t 4800 -h -1 -W | findstr bak>>del_file.txt
rem 开始自动上传(上传完删除对应的本地文件)
echo begin_ftp_upload
call :uploaddir "%LOCAL_DIR%" "%PUT_DIR%"
rem 检查是否上传失败,失败则记录数据库日志
echo begin_check
for /f "delims=" %%i in ('dir /b /a-d "%LOCAL_DIR%*.bak"') do echo %%~nxi>>local_file.txt
if not exist local_file.txt goto :delete_remote
for /f "delims=" %%i in (local_file.txt) do (
sqlcmd -E -S 127.0.0.1,1333 -d master -v FILE_NAME="'%%i'" -Q "insert into t_log_backup(loglevel, filename, remark) values(1,$(FILE_NAME),'严重错误:上传备份文件失败,请检查ftp权限.同时它会导致本地磁盘满.');" -t 3600
)
del /f/q local_file.txt
rem 开始删除远程过期文件
echo begin_del_remote
:delete_remote
call :delremotefile "%LOCAL_DIR%" "%PUT_DIR%"
rem 启动数据库发送邮件(即发送错误日志)
sqlcmd -E -S 127.0.0.1,1333 -d master -Q "execute dbo.p_sendmail;" -t 120
echo end.
goto :eof
rem ---------------------程序结束-------------------------------------------------------------------------
rem ---------------------以下均为调用的子函数-------------------------------------------------------------
:uploaddir - upload directory tree
::         - %~1 local directory
::         - %~2 remote directory
setlocal
set "localdir=%~1"
set "remotedir=%~2"
call :put_autoftp cmdftp_uploaddir "