SQLServer下利用vbscript实现数据库月备份自动清理并发邮件提

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) <> "/" Then

lf=lstg_folder_name& "/" & 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,"/","-")

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)<>"/" then

' lstg_log_folder=lstg_log_folder& "/"

'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,"/","-")

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 批处理文件就行了。