backup.bat

sql server2005的远程备份及ftp上传备份文件的脚本

@ECHO off

REM Backup Database pcbc
REM 20080602修改：保留三天的数据文件

SET B_Database_Name=dbname
SET B_Database_Log=db_log
SET B_DATE=%date:~0,4%%date:~5,2%%date:~8,2%
SET DBUser=user
SET DBpassword=password
SET DBserver=172.16.1.1


set d_week=%date:~11,3%
if %d_week%==星期一  set filename=Monday
if %d_week%==星期二  set filename=Tuesday
if %d_week%==星期三  set filename=Wednesday
if %d_week%==星期四  set filename=Thursday
if %d_week%==星期五  set filename=Friday
if %d_week%==星期六  set filename=Saturday
if %d_week%==星期日  set filename=Sunday
rem if "%d_week%"=="星期日"  set filename=Sunday

rem SET dbbak_dir=t:\backup\
SET dbbak_dir=\\172.16.10.34\sqlbackup\

ECHO use master ;>>FULL_backup.sql
ECHO go >>FULL_backup.sql
echo backup database %B_Database_Name%>>FULL_backup.sql
echo to disk='%dbbak_dir%%filename%.bak'>>FULL_backup.sql
echo WITH INIT , NOUNLOAD ;>>FULL_backup.sql
ECHO go >>FULL_backup.sql
echo use %B_Database_Name%;>>FULL_backup.sql
echo go >>FULL_backup.sql
echo backup log %B_Database_Name% with no_log;>>FULL_backup.sql
echo go >>FULL_backup.sql

echo dbcc shrinkfile(%B_Database_Log%);>>FULL_backup.sql
echo go >>FULL_backup.sql


sqlcmd -U %DBUser% -P %DBpassword% -S %DBserver% -i FULL_backup.sql -o %B_Database_Name%_%B_DATE%_FULL.log

if exist FULL_backup.sql del FULL_backup.sql

REM del
if %d_week%==星期一  if exist Friday.bak del Friday.bak
if %d_week%==星期二  if exist Saturday.bak del Saturday.bak
if %d_week%==星期三  if exist Sunday.bak del Sunday.bak
if %d_week%==星期四  if exist Monday.bak del Monday.bak
if %d_week%==星期五  if exist Tuesday.bak del Tuesday.bak
if %d_week%==星期六  if exist Wednesday.bak del Wednesday.bak
if %d_week%==星期日  if exist Thursday.bak del Thursday.bak

REM 用RAR压缩备份文件到db.rar
C:\progra~1\winrar\winrar a -m5 -k -ep1  c:\backup\db.rar c:\backup\%filename%.bak

REM 从本地上传文件到ftp服务器
ftp.exe -n -s:ftpput.txt