SQL server数据异地备份到OSS

背景需求：

某部门需要将windows机器上的SQL server数据做一个异地备份，经过对现有的软硬件资源评估，决定使用阿里云的OSS存储

方法：利用SQLserver自带的维护计划做指定数据库的备份，通过bat批量执行脚本将备份文件上传到OSS---->(所需环境：python、osscmd、winrar)

问题：bat批执行脚本无法识别 * 这个正则匹配符，导致不能由每天的日期去识别备份文件。

解决方法：通过维护计划制定备份目录，每天备份后，上传到OSS后再将其删除

脚本如下：

@echo off
rem mail:baiyufeideyouxiang@163.com
set MON=%date:~0,4%%date:~5,2%
set DATE=%date:~0,4%%date:~5,2%%date:~8,2%
set TIME=%date%-%time%
set LOGDIR=G:ack_ossdata_back
set DIR=G:ack_ossdata_back
echo "%TIME% 开始执行完毕!">>%LOGDIR%ackup-oss.log
set NAME=某部备份
set DIRNAME=AIS20180831000315
set PY=G:ack_osssoftwarepython27python.exe
set OSSCMD=G:ack_osssoftwareOSS_PYTHON_APIosscmd
set RAR=G:ack_osssoftwarewinrarWinRAR.exe
set BUCKET=oss://basebackup/
set ID=------
set KEY=------
set HOST=oss-cn-hangzhou.aliyuncs.com
 
set FILE=AIS20180831000315
%RAR% a -k -r -s -m1 %DIR%%DATE%-%FILE%.rar %DIR%\%FILE%
set TIME=
set TIME=%date%-%time%
echo "%TIME% %FILE% 压缩文件执行完毕!">>%LOGDIR%ackup-oss.log
%PY% %OSSCMD% mkdir %BUCKET%%NAME%/%MON --id=%ID% --key=%KEY% --host=%HOST%
%PY% %OSSCMD% mp %DIR%%DATE%-%FILE%.rar %BUCKET%%NAME%/%MON%/%DATE%-%FILE%.rar --id=%ID% --key=%KEY% --host=%HOST% --thread_num=30
if errorlevel 1 ( 
    %PY% %OSSCMD% mp %DIR%%DATE%-%FILE%.rar %BUCKET%%NAME%/%MON%/%DATE%-%FILE%.rar --id=%ID% --key=%KEY% --host=%HOST% --thread_num=30
) else (
    set TIME=
        set TIME=%date%-%time%
        echo "%TIME% %FILE% 上传文件执行完毕!">>%LOGDIR%ackup-oss.log
)
del %DIR%%DATE%-%FILE%.rar
set TIME=
set TIME=%date%-%time%
echo "%TIME% %FILE% 删除文件执行完毕!">>%LOGDIR%ackup-oss.log

set FILE=
set FILE=AIS20180831000316
%RAR% a -k -r -s -m1 %DIR%%DATE%-%FILE%.rar %DIR%\%FILE%
set TIME=
set TIME=%date%-%time%
echo "%TIME% %FILE% 压缩文件执行完毕!">>%LOGDIR%ackup-oss.log
%PY% %OSSCMD% mkdir %BUCKET%%NAME%/%MON% --id=%ID% --key=%KEY% --host=%HOST%
%PY% %OSSCMD% mp %DIR%%DATE%-%FILE%.rar %BUCKET%%NAME%/%MON%/%DATE%-%FILE%.rar --id=%ID% --key=%KEY% --host=%HOST%
set TIME=
set TIME=%date%-%time%
echo "%TIME% %FILE% 上传文件执行完毕!">>%LOGDIR%ackup-oss.log
del %DIR%%DATE%-%FILE%.rar
set TIME=
set TIME=%date%-%time%
echo "%TIME% %FILE% 删除文件执行完毕!">>%LOGDIR%ackup-oss.log
echo "-----------%TIME% 执行完毕!---------">>%LOGDIR%ackup-oss.log

SQL server数据异地备份到OSS

相关推荐