【原创】SQL server 2000 自动备份脚本

::+==========================================================+
::+ SQL SERVER 2000 自动备份脚本 +
::+ +
::+ 每天10点进行全备，然后上传到122.120.1.132 FTP服务器 +
::+ +
::+ FTP服务器上必须根据环境变量设置建立好如下目录: +
::+ +
::+ 星期日 +
::+ 星期一 +
::+ 星期二 +
::+ 星期三 +
::+ 星期四 +
::+ 星期五 +
::+ 星期六 +
::+ 备份上星期日 +
::+==========================================================+
@echo off
::环境变量设置
set FTP_LOG_FILE=FTP_UP_%date:~0,10%.log
set SCRIPTS_FILE=db_full_backup.sql
set SCRIPTS_PATH_ROOT=D:1890dbbakscripts
set FULL_DB_BAK_PATH_ROOT=D:1890dbbakdb
set BAK_PATH_DATE=%date:~0,10%
set BAK_PATH_WEEK=%date:~-3%
set BAK_DB_FILE=db_full_%date:~0,10%.bak
set OSQL_PATH=C:Program FilesMicrosoft SQL Server80ToolsBinn
set MSSQL_USER=***
set MSSQL_PASSWD=***
set MSSQL_SERVER=CL-1890ALTIVRM
set MSSQL_DATABASE=VRM
set MSSQL_BAK_LOG_FILE=FULL_DB_%date:~0,10%.log
set FTP_IP=122.120.1.132
set FTP_PORT=21
set FTP_USER=cl1890
set FTP_PASS=***
set FTP_CONFIG_FILE=%SCRIPTS_PATH_ROOT%FTP_UP.ini
set F_L_0=星期日
set F_L_1=星期一
set F_L_2=星期二
set F_L_3=星期三
set F_L_4=星期四
set F_L_5=星期五
set F_L_6=星期六
set F_L_0_bak=备份上星期日

::条件判断
::if not exist %FULL_DB_BAK_PATH_ROOT%%BAK_PATH% mkdir %FULL_DB_BAK_PATH_ROOT%%BAK_PATH%
if not exist %FULL_DB_BAK_PATH_ROOT%%F_L_0% mkdir %FULL_DB_BAK_PATH_ROOT%%F_L_0%
if not exist %FULL_DB_BAK_PATH_ROOT%%F_L_1% mkdir %FULL_DB_BAK_PATH_ROOT%%F_L_1%
if not exist %FULL_DB_BAK_PATH_ROOT%%F_L_2% mkdir %FULL_DB_BAK_PATH_ROOT%%F_L_2%
if not exist %FULL_DB_BAK_PATH_ROOT%%F_L_3% mkdir %FULL_DB_BAK_PATH_ROOT%%F_L_3%
if not exist %FULL_DB_BAK_PATH_ROOT%%F_L_4% mkdir %FULL_DB_BAK_PATH_ROOT%%F_L_4%
if not exist %FULL_DB_BAK_PATH_ROOT%%F_L_5% mkdir %FULL_DB_BAK_PATH_ROOT%%F_L_5%
if not exist %FULL_DB_BAK_PATH_ROOT%%F_L_6% mkdir %FULL_DB_BAK_PATH_ROOT%%F_L_6%

::判断是否空目录
for /f "usebackq delims=个文件" %%i in (`"dir /a %FULL_DB_BAK_PATH_ROOT%%BAK_PATH_WEEK% | findstr 个文件 "`) do set NOTHING_FILES=%%i

if %NOTHING_FILES% GTR 0 (
if "%BAK_PATH_WEEK%" == "星期一" (
rd /s /q %FULL_DB_BAK_PATH_ROOT%%F_L_0_bak%
mkdir %FULL_DB_BAK_PATH_ROOT%%F_L_0_bak%
rename %FULL_DB_BAK_PATH_ROOT%%F_L_0% %FULL_DB_BAK_PATH_ROOT%%F_L_0_bak%
mkdir %FULL_DB_BAK_PATH_ROOT%%F_L_0%
) else (
del /s /q %FULL_DB_BAK_PATH_ROOT%%BAK_PATH_WEEK%*
)
)

::生成备份脚本
echo use %MSSQL_DATABASE%>%SCRIPTS_PATH_ROOT%%SCRIPTS_FILE%
echo go>>%SCRIPTS_PATH_ROOT%%SCRIPTS_FILE%
echo backup database %MSSQL_DATABASE% to disk='%FULL_DB_BAK_PATH_ROOT%%BAK_PATH_WEEK%%BAK_DB_FILE%' WITH INIT>>%SCRIPTS_PATH_ROOT%%SCRIPTS_FILE%
echo go>>%SCRIPTS_PATH_ROOT%%SCRIPTS_FILE%
echo quit>>%SCRIPTS_PATH_ROOT%%SCRIPTS_FILE%

::生成FTP上传脚本
echo open %FTP_IP% %FTP_PORT%>%FTP_CONFIG_FILE%
echo user %FTP_USER%>>%FTP_CONFIG_FILE%
echo %FTP_PASS%>>%FTP_CONFIG_FILE%
if "%BAK_PATH_WEEK%" == "星期一" (
echo /%F_L_0_bak%>>%FTP_CONFIG_FILE%
echo mdelete *>>%FTP_CONFIG_FILE%
echo cd />>%FTP_CONFIG_FILE%
echo rmdir %F_L_0_bak%>>%FTP_CONFIG_FILE%
echo rename %F_L_1% %F_L_0_bak%>>%FTP_CONFIG_FILE%
echo mkdir %F_L_1%>>%FTP_CONFIG_FILE%
echo cd /%BAK_PATH_WEEK%>>%FTP_CONFIG_FILE%
) else (
echo cd /%BAK_PATH_WEEK%>>%FTP_CONFIG_FILE%
echo mdelete *>>%FTP_CONFIG_FILE%
)
echo bi>>%FTP_CONFIG_FILE%
echo mput %FULL_DB_BAK_PATH_ROOT%%BAK_PATH_WEEK%*>>%FTP_CONFIG_FILE%
echo bye>>%FTP_CONFIG_FILE%

::执行备份

"%OSQL_PATH%osql" -S%MSSQL_SERVER% -U%MSSQL_USER% -P%MSSQL_PASSWD% -i%SCRIPTS_PATH_ROOT%%SCRIPTS_FILE% -o%FULL_DB_BAK_PATH_ROOT%%BAK_PATH_WEEK%%MSSQL_BAK_LOG_FILE%

::上传备份
echo =======================开始上传数据到FTP服务器=========================>>%FULL_DB_BAK_PATH_ROOT%%BAK_PATH_WEEK%%MSSQL_BAK_LOG_FILE%
ftp -d -i -n -s:%FTP_CONFIG_FILE%>>%FULL_DB_BAK_PATH_ROOT%%BAK_PATH_WEEK%%MSSQL_BAK_LOG_FILE%

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12270/viewspace-1001792/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12270/viewspace-1001792/