SQL Server uses vbscript to automatically clean up monthly database backup and send an email reminder --- Summary --- this document mainly describes vbscript File Processing and how to send emails under vbscript. It consists of three parts. I. Database Backup II. SqlServer calls vbs to scan the database backup folder, clear
SQL Server uses vbscript to automatically clean up monthly database backup and send an email reminder --- Summary --- this document mainly describes vbscript File Processing and how to send emails under vbscript. It consists of three parts. I. SQL Server database backup II. SQL Server calls vbs to scan and clear the database backup folder
SQL Server uses vbscript to automatically clean up database monthly backup and send an email notification
--- Summary ---
This document mainly talks about vbscript File Processing and how to send emails under vbscript. It consists of three parts.
I. SQL Server database backup
2. SQL Server calls vbs to scan the database backup folder, clean up the old database backup, and only keep the database backup for the current month.
3. Notify the database administrator of the cleared database backup files as attachments.
--- Detailes ---
1. SQL Server database backup is implemented by Job. The database backup file name is named in the date format.
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 calls vbs to clear the backup files of the previous month, write and clear logs, and send emails.
1). database files and clear log folders
2). Clear the program folder of the database log. The following describes the specific usage of each file in the folder.
I) Email contains an email_list.txt file, which records the e-mail recipient's use address.
Each line shows an email address, as shown in the following code:
Email_List.txt
Lilo.zhu@gmail.com
Lilo.zhu@ymail.com
Lilo.zhu@qq.com
Ii) Log is the log file recorded when the database backup file is cleared. The file is named in the form of system date. Log.
Iii) The Backup_Clear.bat file is called by SQL Server in the job.
Step Name: Run Clear Last Month Backup Database Data
Type:Operation System (CmdExec)
Run: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 is a script program that clears non-current-month database backup files, writes and clears logs, and sends emails. The content is as follows:
Backup_Clear.vbs
'------------------------------------------------------------
'Database Backup Data clear'
'Auto Clear Last Month Database Backup Data
'
'Create Date:
'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
Rochelle 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)
Rochelle 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
Email. Send
End Function
--- Remark ---
1. In vbs, the CDN. Message object is used to send emails.
2. It is easy for SQL Server to call the bat batch processing with parameters. It is best to write the parameters in the bat file. SQL Server only needs to call this bat batch file.