SQL Server下利用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 DBType: 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 protected]@[email protected] ii)Log 内是清除数据库备文件时记录的日志文件，文件以系统日期.log 形式命名。 iii) Backup_Clear.bat 文件是供Sql Server 在job 是调用的。 Step Name: Run Clear Last Month Backup Database DataType: Operation System (CmdExec)Run as: SQL Agent Service AccountCommand: D:/TRSYS_DB_BAK_CLEAR/Backup_Clear.bat Backup_Clear.bat d:cd d:/TRSYS_DB_BAK_CLEARBackup_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 = 8Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Dim ObjArgsSet 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 IfEnd 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 IfEnd 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 = "[email protected]" 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") = "[email protected]" .Item(NameSpace&"sendpassword") = "123456" .Update End With Email.Send End Function ---Remark---1. vbs 中发邮件是使用CDO.Message 这个对象。2. Sql Server 调用带参数的bat 批处理容易出问题，最好将参数都写在bat 文件内，Sql Server 只需调用这个bat 批处理文件就行了。