SQL Server 2005部署备份任务

环境：SQL Server 2005

任务：

1，每日凌晨1点给本地sql server做本地完整备份，且只保留7天的本地备份；
2，每日凌晨5点将本地的备份打包上传到存储服务器。

思路：

1，sql server2005不支持SQL Server2000的sqlmaint命令，所以，需要通过数据库维护计划进行备份，而不能一步成型的写bat脚本；
2，备份地址先保存到D:\dbbak\sqltemp，并给每个数据库建立子目录，凌晨1点执行；
3，写批处理，将sqltemp目录下的文件保留一个，并将这个文件rar打包放到D:\dbbak\data目录，并保存七天的备份文件，凌晨3点执行；
4，写一个wsf，将每日data目录的压缩包文件上传到存储服务器，凌晨5点执行。

操作步骤：

1，给SQL Server每日本地完整备份
1）打开management Studio-》管理-》右键 维护计划-》维护计划向导
2）按需选择自己的备份计划，本例备份完整的数据库，并存放在D:\dbbak\sqltemp，并给每个数据库建立子目录，备份文件名为bak，每日1点备份
3） 开启sqlagent代理，测试一下，看是否正常。

2，创建脚本dbbackup.bat,实现思路3，内容如下：

@echo cd \ D: set catalog=D:\dbbak : 保留一天的备份文件 @for /f %%i in ('dir /b/ad %catalog%\sqltemp') do (@for /f "skip=1" %%f in ('dir /b/o-n %catalog%\sqltemp\%%i') do del /s/q %catalog%\sqltemp\%%i\%%f) : 压缩sqltemp子目录文件到data目录 @for /f %%i in ('dir /b/ad %catalog%\sqltemp') do (@for /f %%f in ('dir /b %catalog%\sqltemp\%%i') do (if not exist %catalog%\data\%%i mkdir %catalog%\data\%%i) & rar a -inul -m5 -r -ep1 %catalog%\data\%%i\%%f.rar %catalog%\sqltemp\%%i\%%f) : data目录（本地备份压缩文件）保留7天的备份 @for /f %%i in ('dir /b/ad %catalog%\data') do (@for /f "skip=7" %%f in ('dir /b/o-n %catalog%\data\%%i') do del /s/q %catalog%\data\%%i\%%f)

3，创建db-backup.wsf，实现思路4，内容如下：

<job> <script language="VBScript"> Function execMethod Dim WshShell, oExec WScript.echo "run method" Set WshShell = CreateObject("WScript.Shell") Set oExec = WshShell.Exec("calc") Do While oExec.Status = 0 WScript.Sleep 100 Loop WScript.Echo oExec.Status end function '文件根目录，压缩包目录，FTP命令文件,FTP目标IP function runMethod (folderspec,rarpath,ftpfile,ftpaddr) Dim sdate,datedir,dirname,xcopyDate ' get date path sdate = dateadd("d",d,date) datedir = year(sdate) & right("0" & month(sdate),2) & right("0" & day(sdate),2) xcopyDate = right("0" & month(sdate),2) & "-" & right("0" & day(sdate),2) & "-" & year(sdate) ' if hasdate="TRUE" then ' dirname = filedir & "\" & datedir ' else ' dirname = filedir ' end if Dim fso, f, f1, fc, foldname ,batfile , listname , ftplogfile Dim runstr batfile= rarpath & "runbat.bat" listname = rarpath & "log_" & datedir & ".log" ftplogfile = rarpath & "log_" & datedir & "ftp.log" Set fso = CreateObject("Scripting.FileSystemObject") Set f = fso.GetFolder(folderspec) Set fc = f.SubFolders Set fbat= fso.CreateTextFile(batfile, True) Set flist =fso.CreateTextFile(listname, True) Set fftp=fso.CreateTextFile(ftplogfile, True) Dim i,maxi,alli maxi=1 i=0 alli=0 For Each f1 in fc i=i+1 alli=alli+1 foldname = f1.name flist.WriteLine ( alli & ".. " & foldname & chr(13) ) 'fbat.WriteLine (" rar.exe a " & rarpath & foldname & "_" & datedir &".rar " & folderspec & "\" & foldname & chr(13) ) 'following: 修改为每天上传，并且只上传一个 fbat.WriteLine ("xcopy " & folderspec & foldname & "\*.rar /D:" & xcopyDate &" " & rarpath) 'fbat.WriteLine ("xcopy " & folderspec & foldname & "\*.rar" &" " & rarpath) fbat.WriteLine ("echo " & alli & "------------------- " & foldname & ">>" & ftplogfile & chr(13)) if i=maxi then fbat.WriteLine (" ftp -s:" & ftpfile & " " & ftpaddr & ">>" & ftplogfile & chr(13) ) fbat.WriteLine (" del *.rar" & chr(13) ) i=0 end if Next flist.WriteLine ("total: " & alli & chr(13) ) if i>0 then fbat.WriteLine (" ftp -s:" & ftpfile & " " & ftpaddr & chr(13) ) fbat.WriteLine (" del *.rar" & chr(13)) end if set fso=nothing set f1=nothing set f=nothing set fc=nothing set fbat=nothing set flist=nothing set fftp = nothing Dim oShell Set oShell = WScript.CreateObject ("WSCript.shell") runstr = "cmd /K cd " & rarpath runstr = runstr & " & runbat.bat " ' runstr = runstr & " & ftp -s:" & ftpfile & " " & ftpaddr ' runstr = runstr & " & del " & rarpath & & foldname & ".rar" runstr = runstr & " & exit" ' WScript.Echo runstr oShell.run runstr Set oShell = Nothing end function 'run '文件根目录，压缩包目录，FTP命令文件,FTP目标IP call runMethod ("D:\dbbak\data\","C:\job\dbback_up\db_backup\","c:\job\dbback_up\ftp.conf","11.11.13.14") </script> </job>

ftp.conf内容：

username password cd db59237 bin prompt hash mput *.rar bye

另记得建一个db_backup子目录，用于存放日志。

4，创建计划任务，每日凌晨3点执行dbbackup.bat，每日凌晨5点执行db-backup.wsf
对于sql2000的备份，可以不依赖sql agent，只是创建一个维护计划，并直接在批处理文件中通过sqlmaint方式调用即可。

sql2000 dbbackup.bat内容：

@echo cd \ D: set catalog=D:\sqlbak **if not exist %catalog%\sqltemp mkdir %catalog%\sqltemp** **"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlmaint.exe" -PlanName data -BkUpDB %catalog%\sqltemp -BkUpMedia DISK -CrBkSubDir** rd /s/q %catalog%\sqltemp\msdb @for /f %%i in ('dir /b/ad %catalog%\sqltemp') do (@for /f "skip=1" %%f in ('dir /b/o-n %catalog%\sqltemp\%%i') do del /s/q %catalog%\sqltemp\%%i\%%f) @for /f %%i in ('dir /b/ad %catalog%\sqltemp') do (@for /f %%f in ('dir /b %catalog%\sqltemp\%%i') do (if not exist %catalog%\data\%%i mkdir %catalog%\data\%%i) & rar a -inul -m5 -r -ep1 %catalog%\data\%%i\%%f.rar %catalog%\sqltemp\%%i\%%f) @for /f %%i in ('dir /b/ad %catalog%\data') do (@for /f "skip=7" %%f in ('dir /b/o-n %catalog%\data\%%i') do del /s/q %catalog%\data\%%i\%%f)