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

骑猪看日落 2022-07-12 10:17 1153阅读 0赞

@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 "%localdir%" "%remotedir%" >nul  
rem for /D %%I in ("%localdir%/\*") do (  
rem     call :uploaddir "%localdir%/%%~nxI" "%remotedir%/%%~nxI"  
rem )  这三行为子目录递归,本次不需使用  
type remote\_file.tmp | findstr bak >remote\_file.txt  
del /f/q remote\_file.tmp  
for /f "delims=" %%i in (remote\_file.txt) do (  
del /f/q %localdir%%%i  
)  
del /f/q remote\_file.txt  
endlocal  
goto :eof

:cmdftp\_uploaddir - ftp commands for upload directory tree  
::                - %~1 local directory  
::                - %~2 remote directory  
echo lcd "%~1\*"  
echo cd "%~2"  
echo binary  
echo prompt  
echo mput \*.bak  
echo ls -b  
goto :eof

:put\_autoftp - a complete ftp session  
::           - %\* ftp command generator label and its arguments  
setlocal  
set "putftpin=\_ftpin\_"  
(call :put\_preftp & call :%\* & call :postftp) >"%putftpin%"  
ftp -n <"%putftpin%" >remote\_file.tmp  
del /f/q "%putftpin%"  
endlocal  
goto :eof

:put\_preftp - setup the ftp session  
echo open %PUT\_FTP\_SERVER% %PUT\_FTP\_PORT%  
echo user %PUT\_FTP\_USERNAME% %PUT\_FTP\_PASSWORD%  
goto :eof

:postftp - teardown the ftp session  
echo quit  
goto :eof

:delremotefile - delete overdue file in remote dir  
::                - %~1 local directory  
::                - %~2 remote directory  
setlocal  
set "localdir=%~1"  
set "remotedir=%~2"  
call :put\_autoftp cmdftp\_delfile "%localdir%" "%remotedir%" >nul  
del /f/q del\_file.txt  
endlocal  
goto :eof

:cmdftp\_delfile - ftp commands for delete file  
::                - %~1 remote directory  
echo cd "%~1"  
echo binary  
echo prompt  
for /f "delims=" %%i in (del\_file.txt) do (  
echo delete %%i  
)  
goto :eof