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

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

[e-mail]

[e-mail]

[e-mail]

 

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 = "[e-mail]"

     Email.To = str_mail_address

     Email.Subject = "DataBase Bakup Data Clear Message"

     Email.HTMLBody = "Hi DBA: <br> 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") = "[e-mail]"

     .Item(NameSpace&"sendpassword") = "123456"

     .Update

     End With

     Email.Send

 

End Function

 

 

---Remark---

1.       vbs 中发邮件是使用CDO.Message 这个对象。

2.       Sql Server 调用带参数的bat 批处理容易出问题，最好将参数都写在bat 文件内，Sql Server 只需调用这个bat 批处理文件就行了。