【最佳实践】filezilla软件用bat自动化ftp传输文件

【注意】

注意，要想直接使用；

　　脚本文件为，除C盘外最小的逻辑盘符。

　　比如逻辑盘符有 C: D: E:，应该放到D:dba_toolsftp

　　要被上传文件的路径在：最大逻辑盘符下的：DB_AUTO_BAK

【0】filezilla 下载使用

【0.1】安装配置 filezilla

下载安装就不用说了吧？：官网：https://www.filezilla.cn/

配置：左上角那个闪电，就是启动关闭~~

建个账户，点击上图那个小人头，然后，右下角点击添加

配置该账户传输目录的根目录，即该账户默认传输过来文件的默认存放路径

然后下面还可以做限速和IP防火墙白名单黑名单之类的；这就不配置了；

最后一步：编辑=》设置设置好监听端口

【0.2】防火墙问题

FTP服务器：要对访问过来的IP添加入站规则，对TCP 14150（就是你自己设置的FTP端口）开放；

客户端机器：则需要对入站规则开放，并给予任意端口和协议

【1】ftp_backup.bat

@echo off

cd %~dp0
Rem date:yyyymmdd

set today=%date:~0,4%%date:~5,2%%date:~8,2%
set begin_date=%date%%time%

Rem get backup_path
wmic logicaldisk where drivetype=3 get deviceid>>disk_info.txt
for /f %%i in ('type disk_info.txt') do (set temp=%%i )
set disk=%temp:~,-1%
set dir=DB_AUTO_BAK
set backup_path=%disk%%dir%
echo %backup_path%
del disk_info.txt

Rem get ip addr

setlocal enabledelayedexpansion
for /f "tokens=16" %%i in ('ipconfig ^|find /i "ipv4"^|sort /R') do (echo %%i>>ip.txt)
set ip=
setlocal enabledelayedexpansion
for /f "tokens=*" %%i in (ip.txt) do set ip=!ip!%%i__
set ip=%ip:~,-2%
echo %ip%
del ip.txt


Rem ftp.txt

echo open 10.30.161.190 14150>>ftp.txt
echo backup_test>>ftp.txt
echo eLj*5byKgcqepP5P>>ftp.txt
set current_path=cd /
echo %current_path%>>ftp.txt
set mkdir_path=mkdir %ip%
echo %mkdir_path%>>ftp.txt
set new_remote_path=cd %ip%
echo %new_remote_path%>>ftp.txt


Rem put backupfile
setlocal enabledelayedexpansion
for /r "%backup_path%" %%q in (*%today%*) do (
set ftp_uploadcmd=put %%q
echo !ftp_uploadcmd!>>ftp.txt
)

Rem run ftp.txt

echo quit>>ftp.txt
ftp -i -s:ftp.txt >> ftp_log.txt 2>error_log.txt
del ftp.txt
echo begin:%begin_date%_______end:%date%%time%>>ftp_log.txt
echo.>>ftp_log.txt 

timeout /t 5

【2】安装ftp脚本为windows定时任务

@echo off
color 2
echo.
echo ----------------- ftp_backup_install  --------------------
echo ----------------- ftp_backup_install -------------------- > install.log 
echo.

schtasks /delete /TN ftp_backup /F
schtasks /delete /TN ftp_db_bakcup /F


if exist d: (
set disk=d:
) else (
if exist e: (set disk=e:) else (set disk=f:)
)

schtasks /create /ru SYSTEM /tn "ftp_backup" /tr "%disk%dba_toolsftpftp_backup.bat" /sc daily /st 05:20:00 /F 

IF ERRORLEVEL 1 (ECHO Error: ftp_backup install error
ECHO Error: ftp_backup install error >> install.log 2>&1
goto error)
echo OK
echo OK >> install.log 2>&1

:ok
echo.
echo ------------------- Install Success ---------------------
ping -n 2 127.1>nul
ping -n 2 127.1>nul
ping -n 2 127.1>nul
goto end

:error
echo.
echo.
ping -n 2 127.1>nul
ping -n 2 127.1>nul
ping -n 2 127.1>nul
ping -n 2 127.1>nul
ping -n 2 127.1>nul
ping -n 2 127.1>nul
ping -n 2 127.1>nul
ping -n 2 127.1>nul
ping -n 2 127.1>nul
exit 1

:end
exit

【3】默认定时任务输出位置：c:windowssystem32

【4】删除文件夹下所有目录、子目录的15天前文件

set log_dir="D:db_backup"
 
:: 保留日志天数
set bak_dat=15

echo ______________begin__%date%%time%__________ >>D:dba_toolsdel_log.txt
:: 删除日志文件
forfiles /p %log_dir% /S /M * /D -%bak_dat% /C "cmd /c echo 正在删除@relpath 文件… & echo. & del @file" 1>>D:dba_toolsdel_log.txt 2>>D:dba_toolserror_log.txt
echo ______________end____%date%%time%__________ >>D:dba_toolsdel_log.txt
echo.>>D:dba_toolsdel_log.txt

timeout /t 10

【5】在异备机上使用作业监控异备情况

核心原理：查看每个文件夹下是否有今天的文件，有则成功，没有则失败

create table #temp1(dir_info varchar(200),level int)
declare @parent_path varchar(200)
set @parent_path='D:
emote_backup'
insert into #temp1
exec xp_dirtree @parent_path,1
select *,ROW_NUMBER() over(order by dir_info) as rn into #temp2 from #temp1

declare @rn int,@rn_count int
declare @dir_path varchar(200)

CREATE TABLE #temp_bak (bakfile_name VARCHAR(500),[level] INT,is_file INT)

select @rn=1,@rn_count=COUNT(1) from #temp2
while @rn<=@rn_count
begin
    select @dir_path=@parent_path+dir_info from #temp2 where rn=@rn
    print @dir_path
    
    INSERT INTO #temp_bak
    EXEC xp_dirtree @dir_path,1,1
    if not exists(
        select 1 from #temp_bak
        where LEFT(RIGHT(bakfile_name,19),8)=convert(char(8),getdate(),112)
        and (cast(replace(CONVERT(varchar(100), GETDATE(), 24),':','') as int) - LEFT(RIGHT(bakfile_name,10),6)  between 0 and 1000
        or cast(replace(CONVERT(varchar(100), GETDATE(), 24),':','') as int) - LEFT(RIGHT(bakfile_name,10),6) between 4000 and 5000
        )
    )
    begin
        raiserror(@dir_path,16,1)
        truncate table #temp_bak
    end
    else
        print @dir_path+' is ok!'
        truncate table #temp_bak
    set @rn=@rn+1
end