由于工作需要我需要编写一个sql server 数据库的备份脚本.需要实现的功能是:
1. 能根据计划进行数据库的完全备份/增量备份.
2. 将不同的备份内容存放在不同的备份设备上.(例如每天备份的内容单独存放在一个备份设备上).
3.将备份的内容在其他的机器上面保存一份.
在网络上找了一些资料但是大多数都没有人写过类似的东西.经过我两天的编写和测试现在终于搞定了.
实现的功能是:
1.将需要执行的脚本放到计划任务当中.根据需要调整运行的时间.
2.每次的备份内容生成单独的设备名称用日期文件名进行判断.
3.将备份完的内容ftp到其他计算机上.这样也算是搞一个小的"异地容灾"不至于在一个机器完蛋后.数据库的备份没有了.
下面就是这两个脚本的内容.欢迎大家提供意见也建议.
另外就是本人不负责任何运行这些脚本的后果.但是有任何技术问题欢迎交流.如果是在我有时间和我知道的前提下.
脚本分为两部分1个是完全备份的.另外一个是差异备份的.
[code]@ECHO off
REM !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
REM Differential Backup sql server database
REM email:hao.wangbj@gmail.com
REM blog: http://wanghao.cublog.cn
REM Date :20060905
REM !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
ECHO .
REM Backup Database Script Log Files
SET logdir=c:\1
if not exist %logdir%\nul mkdir %logdir%
REM B_SCRIPT_TARGET
SET B_SCRIPT_TARGET=c:\2
if not exist %B_SCRIPT_TARGET%\nul mkdir %B_SCRIPT_TARGET%
REM B_DataFiles_TARGET
SET B_DataFiles_TARGET=c:\3
if not exist %B_DataFiles_TARGET%\nul mkdir %B_DataFiles_TARGET%
REM Backup Database Name
SET B_Database_Name=testdb
SET B_DATE=%date:~0,4%%date:~5,2%%date:~8,2%
ECHO use master ;>%B_SCRIPT_TARGET%\DIFFERENTIAL_backup.sql
ECHO go >>%B_SCRIPT_TARGET%\DIFFERENTIAL_backup.sql
ECHO SP_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
ECHO go >>%B_SCRIPT_TARGET%\DIFFERENTIAL_backup.sql
ECHO BACKUP DATABASE %B_Database_Name% >>%B_SCRIPT_TARGET%\DIFFERENTIAL_backup.sql
ECHO TO BackupDatabase%B_Database_Name%file%B_DATE%Differential >>%B_SCRIPT_TARGET%\DIFFERENTIAL_backup.sql
ECHO WITH NOINIT , NOUNLOAD , DIFFERENTIAL ; >>%B_SCRIPT_TARGET%\DIFFERENTIAL_backup.sql
ECHO go >>%B_SCRIPT_TARGET%\DIFFERENTIAL_backup.sql
ECHO .
REM osql.exe path
SET OSQL_PATH=C:\Program Files\Microsoft SQL Server\80\Tools\Binn\
REM Database user name and password
SET DBUser=sa
SET DBpassword=sapassword
ECHO BACKUP DATABASE
"%OSQL_PATH%osql.exe" -U %DBUser% -P %DBpassword% -i %B_SCRIPT_TARGET%\DIFFERENTIAL_backup.sql -o %logdir%\backupdatabase%B_DATE%_Differential.log
REM FTP info
set FTP_User=administrator
set FTP_PW=adminpassword
set FTP_IP=127.0.0.1
set FTP_target=/database
ECHO %FTP_User%>%B_SCRIPT_TARGET%\FTP_CMD.txt
ECHO %FTP_PW%>>%B_SCRIPT_TARGET%\FTP_CMD.txt
ECHO bin>>%B_SCRIPT_TARGET%\FTP_CMD.txt
ECHO cd %FTP_target%>>%B_SCRIPT_TARGET%\FTP_CMD.txt
ECHO mput %B_DataFiles_TARGET%\%B_Database_Name%%B_DATE%_Differential.bak>>%B_SCRIPT_TARGET%\FTP_CMD.txt
ECHO BYE>>%B_SCRIPT_TARGET%\FTP_CMD.txt
ftp -i -s:%B_SCRIPT_TARGET%\FTP_CMD.txt %FTP_IP%
REM Delete Temp files
DEL /Q %B_SCRIPT_TARGET%\DIFFERENTIAL_backup.sql
DEL /Q %B_SCRIPT_TARGET%\FTP_CMD.txt[/code]
完全备份:
[code]@ECHO off
REM !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
REM FULL backup sql server database
REM email:hao.wangbj@gmail.com
REM blog: http://wanghao.cublog.cn
REM Date :20060905
REM !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
ECHO .
REM Backup Database Script Log Files
SET logdir=c:\1
if not exist %logdir%\nul mkdir %logdir%
REM B_SCRIPT_TARGET
SET B_SCRIPT_TARGET=c:\2
if not exist %B_SCRIPT_TARGET%\nul mkdir %B_SCRIPT_TARGET%
REM B_DataFiles_TARGET
SET B_DataFiles_TARGET=c:\3
if not exist %B_DataFiles_TARGET%\nul mkdir %B_DataFiles_TARGET%
REM Backup Database Name
SET B_Database_Name=testdb
SET B_DATE=%date:~0,4%%date:~5,2%%date:~8,2%
ECHO use master ;>%B_SCRIPT_TARGET%\FULL_backup.sql
ECHO go >>%B_SCRIPT_TARGET%\FULL_backup.sql
ECHO SP_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
ECHO go >>%B_SCRIPT_TARGET%\FULL_backup.sql
ECHO BACKUP DATABASE %B_Database_Name% >>%B_SCRIPT_TARGET%\FULL_backup.sql
ECHO TO BackupDatabase%B_Database_Name%file%B_DATE%_FULL >>%B_SCRIPT_TARGET%\FULL_backup.sql
ECHO WITH NOINIT , NOUNLOAD ;>>%B_SCRIPT_TARGET%\FULL_backup.sql
ECHO go >>%B_SCRIPT_TARGET%\FULL_backup.sql
ECHO .
REM osql.exe path
SET OSQL_PATH=C:\Program Files\Microsoft SQL Server\80\Tools\Binn\
REM Database user name and password
SET DBUser=sa
SET DBpassword=sapassword
ECHO BACKUP DATABASE
"%OSQL_PATH%osql.exe" -U %DBUser% -P %DBpassword% -i %B_SCRIPT_TARGET%\FULL_backup.sql -o %logdir%\backupdatabase%B_DATE%_Full.log
REM FTP info
set FTP_User=administrator
set FTP_PW=admin_password
set FTP_IP=127.0.0.1
set FTP_target=/database
ECHO %FTP_User%>%B_SCRIPT_TARGET%\FTP_CMD.txt
ECHO %FTP_PW%>>%B_SCRIPT_TARGET%\FTP_CMD.txt
ECHO bin>>%B_SCRIPT_TARGET%\FTP_CMD.txt
ECHO cd %FTP_target%>>%B_SCRIPT_TARGET%\FTP_CMD.txt
ECHO mput %B_DataFiles_TARGET%\%B_Database_Name%%B_DATE%_FULL.bak>>%B_SCRIPT_TARGET%\FTP_CMD.txt
ECHO BYE>>%B_SCRIPT_TARGET%\FTP_CMD.txt
ftp -i -s:%B_SCRIPT_TARGET%\FTP_CMD.txt %FTP_IP%
REM Delete Temp files
DEL /Q %B_SCRIPT_TARGET%\FULL_backup.sql
DEL /Q %B_SCRIPT_TARGET%\FTP_CMD.txt[/code]
