目的:
将WIN2008操作系统上备份的DB2数据库镜像备份至LINUX系统上的备份服务器。即当月备份的数据库备份文件在本地和备份服务器上各保留最近的10份,一个月之前的备份文件,一个月保留一份。
本地环境:操作系统Windows server 2008 R2 Standard ,数据库版本DB2 V9.7
数据库备份服务器环境:操作系统Red Hat Enterprise Linux Server release 5.8 (Tikanga)。
前提条件:数据库备份服务器安装Samba服务器,实现Windows与Linux共享。安装Samba服务器在前一篇文章中有介绍。
脚本代码思路:
1.获取数据库的备份路径。
2.在线整库备份数据库。
3.将备份文件压缩,并删除原备份文件,将压缩文件上传至服务器。
备份脚本1:
@echo off
echo *** %DATE% ***
echo *** %TIME% ***
::初始化本地备份home目录,和服务器备份home目录。Z盘是数据库服务器的共享目录挂在到本地系统的目录。
setTHISDATE=%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%
set homebackupdir=D:\DB2\DB-BACK
set serverhomebackupdir=Z:\DB-BACK
echo backupdate: %THISDATE%
echo homebackupdir:%homebackupdir%
echoserverhomebackupdir:%serverhomebackupdir%
::获取上个月备份目录
set year=%DATE:~0,4%
set curmoth=%DATE:~5,2%
echo curmoth:%curmoth%
if %curmoth% equ 01 ( set lastmoth=12 )else ( echo ----------- )
if %curmoth% equ 02 ( set lastmoth=01 )else ( echo ----------- )
if %curmoth% equ 03 ( set lastmoth=02 )else ( echo ----------- )
if %curmoth% equ 04 ( set lastmoth=03 )else ( echo ----------- )
if %curmoth% equ 05 ( set lastmoth=04 )else ( echo ----------- )
if %curmoth% equ 06 ( set lastmoth=05 )else ( echo ----------- )
if %curmoth% equ 07 ( set lastmoth=06 )else ( echo ----------- )
if %curmoth% equ 08 ( set lastmoth=07 )else ( echo ----------- )
if %curmoth% equ 09 ( set lastmoth=08 )else ( echo ----------- )
if %curmoth% equ 10 ( set lastmoth=09 )else ( echo ----------- )
if %curmoth% equ 11 ( set lastmoth=10 )else ( echo ----------- )
if %curmoth% equ 12 ( set lastmoth=11 ) else( echo ----------- )
echo lastmoth:%lastmoth%
set lastmothbackupfilesdir=%homebackupdir%\%year%%lastmoth%
echolastmothbackupfilesdir:%lastmothbackupfilesdir%
::end 获取上个月备份目录
::获取当前备份目录,如果是月初1号,则分别在本地和服务器上创建本月的备份目录,并删除上个月的备份文件,只保留一份。
set backupmoth=%DATE:~0,4%%DATE:~5,2%
setbackupfilesdir=%homebackupdir%\%backupmoth%
setserverbackupdir=%serverhomebackupdir%\%backupmoth%
echo serverbackupdir:%serverbackupdir%
echo backupfilesdir:%backupfilesdir%
d:
cd %homebackupdir%
cd
if exist %backupmoth% (
echobackupfilesdir exist ) else (
mkdir%backupmoth%
mkdir%serverhomebackupdir%\%backupmoth%
cd%lastmothbackupfilesdir%
cd
echodelete %year%%lastmoth% backup only save one backup file
::这里为什么是d -2,我不知道怎么解释,当d-1时会删除所有文件,d -2会保留一个文件。
forfiles/p "%lastmothbackupfilesdir%" /m *.zip /d -2 /c "cmd /c del@path"
forfiles/p "%serverhomebackupdir%\%year%%lastmoth%" /m *.zip /d -2 /c"cmd /c del @path"
iferrorlevel 0 (echo delete %year%%lastmoth% backupfiles success) else (echodelete %year%%lastmoth% backupfiles fail)
)
exit
以上即备份脚本1,实现获取备份目录的作用。
备份脚本2:
实现在线备份数据库。
@echo off
set curmoth=%DATE:~0,4%%DATE:~5,2%
set homebackupdir=D:\DB2\DB-BACK
setbackupfilesdir=%homebackupdir%\%curmoth%
"C:\Program Files\IBM\SQLLIB\BIN\db2cmd.exe" db2 backup db 数据库名 online to "%backupfilesdir%" include logs
exit
注意:db2cmd.exe :为你安装db2的路径下的可执行程序。我这里是安装在C:\Program Files\IBM\SQLLIB\BIN\目录。
备份脚本3:
压缩数据库备份文件并复制至数据库备份服务器。还有删除当前备份路径下10天前的备份文件。
@echo off
set curmoth=%DATE:~0,4%%DATE:~5,2%
set homebackupdir=D:\DB2\DB-BACK
setbackupfilesdir=%homebackupdir%\%curmoth%
set serverhomebackupdir=Z:\ DB-BACK
set serverbackupdir=%serverhomebackupdir%\%curmoth%
echo ----- compression the backup files-----
d:
cd %backupfilesdir%
::获取数据库备份文件名
dir /a /b *.001 >tmp.txt
set /p "zipname="<"tmp.txt"
echo zipname:%zipname%
"C:\Program Files(x86)\WinRAR\Rar.exe" a %zipname%.zip %zipname%
xcopy %zipname%.zip %serverbackupdir%
del %zipname%
del tmp.txt
::删除当前备份目录下10天前的备份文件
d:
cd %backupfilesdir%
cd
echo ------ delete %backupfilesdir% backupfiles 10 days ago------
forfiles /p "%backupfilesdir%" /m*.zip /d -10 /c "cmd /c del @path"
forfiles /p "%serverbackupdir%"/m *.zip /d -10 /c "cmd /c del @path"
if errorlevel 0 ( echo delete delete%backupfilesdir% backup files 10 days ago success ) else ( echo delete%backupfilesdir% backup files 10 days ago fail )
exit
至此,备份脚本全部写完。
还有最后一步,就是将这三个脚本添加到系统的定时任务。每个脚本定时作业之间的时间间隔需要根据你的具体情况设定。我这里将定时作业1与定时作业2之间的时间设为5分钟,定时作业2与定时作业3之间的时间间隔设为15分钟。