Windows – SQL Backup script with mail notification

Prerequisites

1 Windows server with MSSQL and Management studio
1 Batch file
1 Vb script file
1 Scheduled task 🙂

Step 1: Create batch file SQLBCKPNL.bat



Copy this code to your batch file and edit your own parameters

#Delete log file d:\SQLBCKPNL.txt 
del d:\SQLBCKPNL.txt

#Delete files in folder more than 5 days 
forfiles -p "D:\SQLBCKPNL\AFF01" -s -m *.* /D -5 /C "cmd /c del /Q /S @path"
forfiles -p "D:\SQLBCKPNL\odb01" -s -m *.* /D -5 /C "cmd /c del /Q /S @path"

#This syntax for single DB name like "yoursite"
osql -S AFF01 -E -Q "backup database yoursite TO DISK='\\Sqlbckpnl01\sqlbckpnl$\AFF01\yoursite.bak' with init" >>d:\SQLBCKPNL.txt
ren "D:\SQLBCKPNL\AFF01\yoursite.bak" "yoursite-%date:/=-% %time::=-%.bak"

#This syntax for not regular DB name like "yourwebsite.com"
osql -S odb01 -E -Q "backup database [yourwebsite.com] TO DISK='\\Sqlbckpnl01\sqlbckpnl$\odb01\yourwebsite.com.bak' with init" >>d:\SQLBCKPNL.txt
ren "D:\SQLBCKPNL\odb01\yourwebsite.com.bak" "yourwebsite.com-%date:/=-% %time::=-%.bak"

#This syntax running after SQL backup and send notification to admin
wscript "C:\New-Version-Enabled-Email-Alert.vbs"

Step 2: Create vbscript New-Version-Enabled-Email-Alert.vbs

Dim objEmail
Set objEmail = CreateObject("CDO.Message")

'************************************
'** Seting basic email information **
'************************************
Const EmailFrom = "[email protected] "
Const EmailTo = "[email protected]"
Const EmailSubject = "SQLBCKPNL backup status"

'***************************************
'** Setting Mail Server Configuration **
'***************************************
Const MailSendUsing = "2"
Const MailSendServer = "your.smtp.server.1"
Const MailSendPort = "25"
'----- for gmail-----
'Const MailSendUsername = "[email protected]"
'Const MailSendPassword = "mypassword"
'Const MailSendAuthenticationType = "1"

'**************************************
'** Email Parameters (DO NOT CHANGE) **
'**************************************
objEmail.From = EmailFrom
objEmail.To = EmailTo
objEmail.Subject = EmailSubject
objEmail.Textbody = EmailBody
objEmail.AddAttachment EmailAttachments
objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = MailSendUsing
ObjEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = MailSendServer
objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = MailSendPort
'objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = MailSendAuthenticationType
'objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = MailSendUsername
'objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = MailSendPassword

'*******************************************************
'** Setting a text file to be shown in the email Body **
'*******************************************************
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
'** File to be inserted in Body
Const FileToBeUsed = "D:\SQLBCKPNL.txt"
Dim fso, f
Set fso = CreateObject("Scripting.FileSystemObject")
'** Open the file for reading
Set f = fso.OpenTextFile(FileToBeUsed, ForReading)
'** The ReadAll method reads the entire file into the variable BodyText
objEmail.Textbody = f.ReadAll
'** Close the file
f.Close
'** Clear variables
Set f = Nothing
Set fso = Nothing

'* cdoSendUsingPickup (1)
'* cdoSendUsingPort (2)
'* cdoSendUsingExchange (3)

'********************************
'** Parameters (DO NOT CHANGE) **
'********************************
ObjEmail.Configuration.Fields.Update
objEmail.Send

Step 3: Setup Schedual Task

Enjoy

🙂