数据库备份（ms sql server）

@ECHO off

ECHO database bakup start!

REM Backup Database Script Log Files
SET dbbak_dir=e:\cnad_online\
SET dbbak_dir2=e:\cnad_online
SET target_dir="C:\Program Files\Apache Group\Tomcat 5.0\webapps\log\"
SET target_dir2="C:\Program Files\Apache Group\Tomcat 5.0\webapps\log"
if not exist %dbbak_dir% mkdir %dbbak_dir%

REM Backup Database Name
SET Database_Name=cnad
SET Date_Today=%date:~0,4%%date:~5,2%%date:~8,2%

ECHO use master ;>%dbbak_dir%backup_%Date_Today%.sql
ECHO go >>%dbbak_dir%backup_%Date_Today%.sql
rem ECHO SP_ADDUMPDEVICE 'DISK',>>%dbbak_dir%backup_%Date_Today%.sql
rem ECHO '%Database_Name%_%Date_Today%', >>%dbbak_dir%backup_%Date_Today%.sql
rem ECHO '%dbbak_dir%%Database_Name%_%Date_Today%.bak'; >>%dbbak_dir%backup_%Date_Today%.sql
rem ECHO go >>%dbbak_dir%backup_%Date_Today%.sql
ECHO BACKUP DATABASE %Database_Name% >>%dbbak_dir%backup_%Date_Today%.sql
ECHO TO DISK = '%dbbak_dir%%Database_Name%_%Date_Today%.bak' >>%dbbak_dir%backup_%Date_Today%.sql
ECHO WITH INIT , NOUNLOAD ;>>%dbbak_dir%backup_%Date_Today%.sql
ECHO go >>%dbbak_dir%backup_%Date_Today%.sql


REM ECHO use master ;>>%dbbak_dir%backup_%Date_Today%.sql
REM ECHO go >>%dbbak_dir%backup_%Date_Today%.sql
REM ECHO BACKUP LOG %Database_Name% >>%dbbak_dir%backup_%Date_Today%.sql
REM ECHO TO DISK = '%dbbak_dir%%Database_Name%_%Date_Today%_log.bak' >>%dbbak_dir%backup_%Date_Today%.sql



REM osql.exe path
SET OSQL_PATH=C:\Program Files\Microsoft SQL Server\90\Tools\Binn\

REM Database user name and password
SET DBUser=**
SET DBpassword=***
ECHO BACKUP DATABASE
"%OSQL_PATH%osql.exe" -U %DBUser% -P %DBpassword% -i %dbbak_dir%backup_%Date_Today%.sql -o %dbbak_dir%%Database_Name%_%Date_Today%.log

ECHO database bakup complete!




REM compress file
ECHO rar start!>>%dbbak_dir%%Database_Name%_%Date_Today%.log
rar a -ad  -m1 %dbbak_dir%%Database_Name%_%Date_Today%.rar %dbbak_dir%%Database_Name%_%Date_Today%.bak
rar a -ad  -m1 %dbbak_dir%%Database_Name%_%Date_Today%.rar %dbbak_dir%backup_%Date_Today%.sql
rar a -ad  -m1 %dbbak_dir%%Database_Name%_%Date_Today%.rar %dbbak_dir%%Database_Name%_%Date_Today%.log
ECHO rar a -ad  -m1 %dbbak_dir%%Database_Name%_%Date_Today%.rar %dbbak_dir%%Database_Name%_%Date_Today%.bak>>%dbbak_dir%%Database_Name%_%Date_Today%.log
ECHO rar a -ad  -m1 %dbbak_dir%%Database_Name%_%Date_Today%.rar %dbbak_dir%backup_%Date_Today%.sql>>%dbbak_dir%%Database_Name%_%Date_Today%.log
ECHO rar a -ad  -m1 %dbbak_dir%%Database_Name%_%Date_Today%.rar %dbbak_dir%%Database_Name%_%Date_Today%.log>>%dbbak_dir%%Database_Name%_%Date_Today%.log
ECHO rar complete!

REM move file to target directory
ECHO copy start!>>%dbbak_dir%%Database_Name%_%Date_Today%.log
copy %dbbak_dir%%Database_Name%_%Date_Today%.rar %target_dir%
ECHO copy %dbbak_dir%%Database_Name%_%Date_Today%.rar %target_dir%>>%dbbak_dir%%Database_Name%_%Date_Today%.log
ECHO copy complete!

REM delete file
ECHO del start!>>%dbbak_dir%%Database_Name%_%Date_Today%.log
DEL /Q %dbbak_dir%%Database_Name%_%Date_Today%.bak
DEL /Q %dbbak_dir%backup_%Date_Today%.sql
REM  /Q %dbbak_dir%%Database_Name%_%Date_Today%.log
ECHO DEL /Q %dbbak_dir%%Database_Name%_%Date_Today%.bak>>%dbbak_dir%%Database_Name%_%Date_Today%.log
ECHO DEL /Q %dbbak_dir%backup_%Date_Today%.sql>>%dbbak_dir%%Database_Name%_%Date_Today%.log


REM delete file which is one day ago
FORFILES /P %dbbak_dir2% /D -2 -M %Database_Name%*.rar /C "cmd /c del @file"
FORFILES /P %target_dir2% /D -1 -M %Database_Name%*.rar /C "cmd /c del @file"
ECHO FORFILES /P %dbbak_dir2% /D -2 -M %Database_Name%*.rar /C "cmd /c del @file">>%dbbak_dir%%Database_Name%_%Date_Today%.log
ECHO FORFILES /P %target_dir2% /D -1 -M %Database_Name%*.rar /C "cmd /c del @file">>%dbbak_dir%%Database_Name%_%Date_Today%.log


ECHO del complete!