SQL Server uses vbscript to automatically clean up database monthly backups and send emails

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.