SQL Server下利用vbscript 实现数据库月备份自动清理并发邮件提醒 ---Summary--- 这份文档其实主要是讲vbscript的文件处理和在vbscript 下如何发邮件。大至分为三个部分。 一．Sql Server 数据库备份 二．Sql Server 调用vbs对数据库备份文件夹进行扫描，清
SQL Server下利用vbscript 实现数据库月备份自动清理并发邮件提醒
---Summary---
这份文档其实主要是讲vbscript的文件处理和在vbscript 下如何发邮件。大至分为三个部分。
一．Sql Server 数据库备份
二．Sql Server 调用vbs对数据库备份文件夹进行扫描，清理以前旧的数据库备份，只保留当前月的数据库备份。
三．将清除的数据库备份文件以附件的形式通知数据库管理员。
---Detailes---
1. Sql Server 数据库备份，以Job 的方式来实现，数据库备份文件名以日期的格式命名。
Step Name: Bakup Training DB
Type: Transact-SQL Script(T-SQL)
Database: ASATTraining
Command:
declare @filename nvarchar(100)
set @filename='D:/TRSYS_DB_BAK/'+convert(char(10),getdate(),112)
print @filename
BACKUP DATABASE [ASATTraining] TO DISK = @filename WITH NOINIT , NOUNLOAD , NAME = N'ASATTraining', NOSKIP , STATS = 10, NOFORMAT
2. Sql Server 调用vbs实现清理上个月的备份文件，写清理日志，发邮件。
1).数据库文件和清理日志文件夹
2).清理数据库日志的程序文件夹,下面就分别讲讲这个文件夹内每个文件的具体用途。
i)Email 里有一个Email_List.txt 的文件，它是记录邮件接受者的用件地址。
每行显示一个邮件地址，具体如下：
Email_List.txt
lilo.zhu@gmail.com
lilo.zhu@ymail.com
lilo.zhu@qq.com
ii)Log 内是清除数据库备文件时记录的日志文件，文件以系统日期.log 形式命名。
iii) Backup_Clear.bat 文件是供Sql Server 在job 是调用的。
Step Name: Run Clear Last Month Backup Database Data
Type: Operation System (CmdExec)
Run as: SQL Agent Service Account
Command: D:/TRSYS_DB_BAK_CLEAR/Backup_Clear.bat
Backup_Clear.bat
d:
cd d:/TRSYS_DB_BAK_CLEAR
Backup_Clear.vbs D:/TRSYS_DB_BAK
iv) Backup_Clear.vbs 是实现在清除非本月数据库备份文件，写清除日志，发送邮件的具体脚本程序,内容如下：
Backup_Clear.vbs
'------------------------------------------------------------
'DataBase Backup Data Clear
'Auto Clear Last Month Database Backup Data
'
'Create Date: 2009-09-29
'Author: Wei_Zhu
'Chage Log:
'Last Chage Date:
'-------------------------------------------------------------
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim ObjArgs
Set ObjArgs = Wscript.Arguments
set ws=createobject("wscript.shell")
If ObjArgs.Count > 0 then
'WSH.Echo "The Folder Name is: "& ObjArgs(0)
ShowFolderList(ObjArgs(0))
else
Wscript.Echo "Please Input The Full Folder Path..."
end if
Function ShowFolderList(ByVal lstg_folder_name)
Dim fso, f, f1, fc, s, folder
Dim l_count
l_count = 1
fso = CreateObject("Scripting.FileSystemObject")
folder = fso.getfolder(ws.currentdirectory)
f = fso.GetFolder(lstg_folder_name)
fc = f.files
For Each f1 In fc
Dim ldt, lf
ldt = int(mid(f1.name, 5, 2))
'msgbox Month(Now) &" || " & Month(ldt)
If Month(Now) > ldt Then
If right(lstg_folder_name, 1) <> "//m.sbmmt.com/m/" Then
lf=lstg_folder_name& "//m.sbmmt.com/m/" & f1.name
End If
DeleteFile(lf)
l_count = l_count + 1
End If
Next
If l_count > 0 Then
l_email_address = folder & "/Email/Email_List.txt"
SendEmail(l_email_address)
End If
End Function
Function DeleteFile(ByVal lstg_file_name)
Dim lstg_msg
fso = CreateObject("Scripting.FileSystemObject")
f = fso.GetFile(lstg_file_name)
f.Delete()
'msgbox(lstg_file_name & " delete file success !")
lstg_msg = Now & " Last Month Database Backup File: " & lstg_file_name & " Delete Success !"
WriteLog(lstg_msg)
End Function
Function WriteLog(ByVal lstg_log_msg)
Dim fso, f, LogFile, l_file_name, folder, l_email_address
dt=replace(date,"//m.sbmmt.com/m/","-")
fso = CreateObject("Scripting.FileSystemObject")
folder = fso.getfolder(ws.currentdirectory)
'---Check Log Folder Exists---
'If (Not fso.FolderExists(lstg_log_folder)) Then
' fso.CreateFolder(lstg_log_folder)
'End If
'if right(lstg_log_folder,1)<>"//m.sbmmt.com/m/" then
' lstg_log_folder=lstg_log_folder& "//m.sbmmt.com/m/"
'end if
l_file_name=folder& "/Log/" & dt & ".log"
'---Check Log File Exists---
If (fso.FileExists(l_file_name)) Then
f = fso.GetFile(l_file_name)
LogFile = f.OpenAsTextStream(ForAppending, TristateUseDefault)
LogFile.WriteLine(lstg_log_msg)
LogFile.Close()
Else
LogFile = fso.CreateTextFile(l_file_name, True)
LogFile.WriteLine(lstg_log_msg)
LogFile.Close()
End If
End Function
Function SendEmail(ByVal lstg_email_list)
dt=replace(date,"//m.sbmmt.com/m/","-")
Dim fso, Email_File, folder, str_mail_address, Attached_File
fso = CreateObject("Scripting.FileSystemObject")
'msgbox lstg_email_list
Email_File = fso.OpenTextFile(lstg_email_list, ForReading, False)
Do While Email_File.AtEndOfStream <> True
str_mail_address = str_mail_address & Email_File.ReadLine & ";"
Loop
Email_File.Close()
'msgbox str_mail_address
folder = fso.getfolder(ws.currentdirectory)
if fso.FileExists(folder& "/Log/" & dt & ".log") then
Attached_File=folder& "/Log/" & dt & ".log"
End If
'msgbox Attached_File
'---Send Emial---
NameSpace = "http://schemas.microsoft.com/cdo/configuration/"
Set Email = CreateObject("CDO.Message")
Email.From = "lilo.zhu@ymail.com"
Email.To = str_mail_address
Email.Subject = "DataBase Bakup Data Clear Message"
Email.HTMLBody = "Hi DBA:
Clear Last Month Database Backup Data Complete,Please See Teh Attached File..."
if Attached_File <> "" then
Email.AddAttachment Attached_File
end if
With Email.Configuration.Fields
.Item(NameSpace&"sendusing") = 2
.Item(NameSpace&"smtpserver") = "smtp.mail.yahoo.com"
.Item(NameSpace&"smtpserverport") = 25
.Item(NameSpace&"smtpauthenticate") = 1
.Item(NameSpace&"sendusername") = "lilo.zhu@ymail.com"
.Item(NameSpace&"sendpassword") = "123456"
.Update
End With
Email.Send
End Function
---Remark---
1. vbs 中发邮件是使用CDO.Message 这个对象。
2. Sql Server 调用带参数的bat 批处理容易出问题，最好将参数都写在bat 文件内，Sql Server 只需调用这个bat 批处理文件就行了。
