由于工作需要我需要编写一个sqlserver数据库的备份脚本.需要实现的功能是:
1.能根据计划进行数据库的完全备份/增量备份.
2.将不同的备份内容存放在不同的备份设备上.(例如每天备份的内容单独存放在一个备份设备上).
3.将备份的内容在其他的机器上面保存一份.
在网络上找了一些资料但是大多数都没有人写过类似的东西.经过我两天的编写和测试现在终于搞定了.
实现的功能是:
1.将需要执行的脚本放到计划任务当中.根据需要调整运行的时间.
2.每次的备份内容生成单独的设备名称用日期文件名进行判断.
3.将备份完的内容ftp到其他计算机上.这样也算是搞一个小的"异地容灾"不至于在一个机器完蛋后.数据库的备份没有了.
下面就是这两个脚本的内容.欢迎大家提供意见也建议.
另外就是本人不负责任何运行这些脚本的后果.但是有任何技术问题欢迎交流.如果是在我有时间和我知道的前提下.
脚本分为两部分1个是完全备份的.另外一个是差异备份的.
@ECHOoff
REM!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
REMDifferentialBackupsqlserverdatabase
REMemail:hao.wangbj@gmail
REMblog:wanghao.cublog
REMDate:20060905
REM!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
ECHO.
REMBackupDatabaseScriptLogFiles
SETlogdir=c:\1
ifnotexist%logdir%\nulmkdir%logdir%
REMB_SCRIPT_TARGET
SETB_SCRIPT_TARGET=c:\2
ifnotexist%B_SCRIPT_TARGET%\nulmkdir%B_SCRIPT_TARGET%
REMB_DataFiles_TARGET
SETB_DataFiles_TARGET=c:\3
ifnotexist%B_DataFiles_TARGET%\nulmkdir%B_DataFiles_TARGET%
REMBackupDatabaseName
SETB_Database_Name=testdb
SETB_DATE=%date:~0,4%%date:~5,2%%date:~8,2%
ECHOusemaster;>%B_SCRIPT_TARGET%\DIFFERENTIAL_backup.sql
ECHOgo>>%B_SCRIPT_TARGET%\DIFFERENTIAL_backup.sql
ECHOSP_ADDUMPDEVICE'DISK',>>%B_SCRIPT_TARGET%\DIFFERENTIAL_backup.sql
ECHO'BackupDatabase%B_Database_Name%file%B_DATE%Differential',>>%B_SCRIPT_TARGET%\DIFFERENTIAL_backup.sql
ECHO'%B_DataFiles_TARGET%\%B_Database_Name%%B_DATE%_Differential.bak';>>%B_SCRIPT_TARGET%\DIFFERENTIAL_backup.sql
ECHOgo>>%B_SCRIPT_TARGET%\DIFFERENTIAL_backup.sql
ECHOBACKUPDATABASE%B_Database_Name%>>%B_SCRIPT_TARGET%\DIFFERENTIAL_backup.sql
ECHOTOBackupDatabase%B_Database_Name%file%B_DATE%Differential>>%B_SCRIPT_TARGET%\DIFFERENTIAL_backup.sql
ECHOWITHNOINIT,NOUNLOAD,DIFFERENTIAL;>>%B_SCRIPT_TARGET%\DIFFERENTIAL_backup.sql
ECHOgo>>%B_SCRIPT_TARGET%\DIFFERENTIAL_backup.sql
ECHO.
REMosql.exepath
SETOSQL_PATH=C:\ProgramFiles\MicrosoftSQLServer\80\Tools\Binn\
REMDatabaseusernameandpassword
SETDBUser=sa
SETDBpassword=sapassword
ECHOBACKUPDATABASE
"%OSQL_PATH%osql.exe"-U%DBUser%-P%DBpassword%-i%B_SCRIPT_TARGET%\DIFFERENTIAL_backup.sql-o%logdir%\backupdatabase%B_DATE%_Differential.log
REMFTPinfo
setFTP_User=administrator
setFTP_PW=adminpassword
setFTP_IP=127.0.0.1
setFTP_target=/database
ECHO%FTP_User%>%B_SCRIPT_TARGET%\FTP_CMD.txt
ECHO%FTP_PW%>>%B_SCRIPT_TARGET%\FTP_CMD.txt
ECHObin>>%B_SCRIPT_TARGET%\FTP_CMD.txt
ECHOcd%FTP_target%>>%B_SCRIPT_TARGET%\FTP_CMD.txt
ECHOmput%B_DataFiles_TARGET%\%B_Database_Name%%B_DATE%_Differential.bak>>%B_SCRIPT_TARGET%\FTP_CMD.txt
ECHOBYE>>%B_SCRIPT_TARGET%\FTP_CMD.txt
ftp-i-s:%B_SCRIPT_TARGET%\FTP_CMD.txt%FTP_IP%
REMDeleteTempfiles
DEL/Q%B_SCRIPT_TARGET%\DIFFERENTIAL_backup.sql
DEL/Q%B_SCRIPT_TARGET%\FTP_CMD.txt
完全备份:
@ECHOoff
REM!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
REMFULLbackupsqlserverdatabase
REMemail:hao.wangbj@gmail
REMblog:wanghao.cublog
REMDate:20060905
REM!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
ECHO.
REMBackupDatabaseScriptLogFiles
SETlogdir=c:\1
ifnotexist%logdir%\nulmkdir%logdir%
REMB_SCRIPT_TARGET
SETB_SCRIPT_TARGET=c:\2
ifnotexist%B_SCRIPT_TARGET%\nulmkdir%B_SCRIPT_TARGET%
REMB_DataFiles_TARGET
SETB_DataFiles_TARGET=c:\3
ifnotexist%B_DataFiles_TARGET%\nulmkdir%B_DataFiles_TARGET%
REMBackupDatabaseName
SETB_Database_Name=testdb
SETB_DATE=%date:~0,4%%date:~5,2%%date:~8,2%
ECHOusemaster;>%B_SCRIPT_TARGET%\FULL_backup.sql
ECHOgo>>%B_SCRIPT_TARGET%\FULL_backup.sql
ECHOSP_ADDUMPDEVICE'DISK',>>%B_SCRIPT_TARGET%\FULL_backup.sql
ECHO'BackupDatabase%B_Database_Name%file%B_DATE%_FULL',>>%B_SCRIPT_TARGET%\FULL_backup.sql
ECHO'%B_DataFiles_TARGET%\%B_Database_Name%%B_DATE%_FULL.bak';>>%B_SCRIPT_TARGET%\FULL_backup.sql
ECHOgo>>%B_SCRIPT_TARGET%\FULL_backup.sql
ECHOBACKUPDATABASE%B_Database_Name%>>%B_SCRIPT_TARGET%\FULL_backup.sql
ECHOTOBackupDatabase%B_Database_Name%file%B_DATE%_FULL>>%B_SCRIPT_TARGET%\FULL_backup.sql
ECHOWITHNOINIT,NOUNLOAD;>>%B_SCRIPT_TARGET%\FULL_backup.sql
ECHOgo>>%B_SCRIPT_TARGET%\FULL_backup.sql
ECHO.
REMosql.exepath
SETOSQL_PATH=C:\ProgramFiles\MicrosoftSQLServer\80\Tools\Binn\
REMDatabaseusernameandpassword
SETDBUser=sa
SETDBpassword=sapassword
ECHOBACKUPDATABASE
"%OSQL_PATH%osql.exe"-U%DBUser%-P%DBpassword%-i%B_SCRIPT_TARGET%\FULL_backup.sql-o%logdir%\backupdatabase%B_DATE%_Full.log
REMFTPinfo
setFTP_User=administrator
setFTP_PW=admin_password
setFTP_IP=127.0.0.1
setFTP_target=/database
ECHO%FTP_User%>%B_SCRIPT_TARGET%\FTP_CMD.txt
ECHO%FTP_PW%>>%B_SCRIPT_TARGET%\FTP_CMD.txt
ECHObin>>%B_SCRIPT_TARGET%\FTP_CMD.txt
ECHOcd%FTP_target%>>%B_SCRIPT_TARGET%\FTP_CMD.txt
ECHOmput%B_DataFiles_TARGET%\%B_Database_Name%%B_DATE%_FULL.bak>>%B_SCRIPT_TARGET%\FTP_CMD.txt
ECHOBYE>>%B_SCRIPT_TARGET%\FTP_CMD.txt
ftp-i-s:%B_SCRIPT_TARGET%\FTP_CMD.txt%FTP_IP%
REMDeleteTempfiles
DEL/Q%B_SCRIPT_TARGET%\FULL_backup.sql
DEL/Q%B_SCRIPT_TARGET%\FTP_CMD.txt
