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