清除sql server 数据库缓存有什么作用 怎么清理sql server数据库

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:
declarenvarchar(100)
set='D:/TRSYS_DB_BAK/'+convert(char(10),getdate(),112)
print @filename 
BACKUPDATABASE [ASATTraining] TO DISK = @filename WITH NOINIT , NOUNLOAD , NAME = N'ASATTraining', NOSKIP , STATS = 10,

2. Sql Server 调用vbs实现清理上个月的备份文件，写清理日志，发邮件。

1).数据库文件和清理日志文件夹

 

2).清理数据库日志的程序文件夹,下面就分别讲讲这个文件夹内每个文件的具体用途。

 

 

 

i)Email里有一个Email_List.txt 的文件，它是记录邮件接受者的用件地址。

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"wscript.shell") 
If  then
'WSH.Echo "The Folder Name is: "& ObjArgs(0)
     ShowFolderList(ObjArgs(0))
else
     Wscript.Echo "Please Input The Full Folder Path..."
endif
 
FunctionByVal
    Dim
    Dim
    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
        Dim
        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& "/"
            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
EndFunction
 
FunctionByVal
    Dim
    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)
EndFunction
 
FunctionByVal
    Dim
   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
EndFunction
 
FunctionByVal
   dt=replace(date,"/","-")
    Dim
    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
     .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
 
EndFunction
 
 
---Remark---

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

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