SQL Backup Schedule & Email Notification

SQL Backup Schedule & Email Notification

Creating a SQL Backup schedule using the SQL Server Agent is very straightforward however we don’t want to check this manually by logging into the SQL Server and checking the success Logs every day / week / month. We can in fact trigger a Powershell command to send us an email on the success or failure of an automated backup.

1. Ensure the SQL Server Agent is started.

2015-09-25 10_29_10-Microsoft SQL Server Management Studio

2. Create a new Job.

2015-09-25 10_30_01-Microsoft SQL Server Management Studio

3. Give the Job a name.

JobProperties

4. Add a new Step.

5. Add the Backup command with the DB Name / Location. >> OK

BACKUP DATABASE [Demo Database NAV (7-1)] TO DISK=’C:\temp\Navision_Cronus71.bak’ WITH INIT

 

6. Create another step for ‘Success’. >> Set the Type as ‘Powershell’ >> Run As SQL Server Agent Service Account

This is an example powershell script – you may need to amend this to work with your type of SMTP server. You should speak to your IT department.

$OFS = “`r`n”
$timestamp = Get-Date;
$smtp = New-Object Net.Mail.SmtpClient(“localhost”)
$smtp.Send(“webserver@example.co.uk”,”recipient@metaphorix.co.uk”,” SQL Backup Subject – ” + $timestamp,
“Backup completed successfully.” + $OFS )

Note: Advanced tab >> on Success >> Quit. On Failure >> Quit.

7. Do the same for a ‘Failure’ step and change the content of the email to be ‘Failure’.

Note: Advanced tab >> on Success >> Quit. On Failure >> Quit.

8. Important! – Make sure to go back to Step 1 ‘Backup’>> Advanced >> On Success go to Step 2 / On Failure go to Step 3.

9. Create a schedule. >> Set this to daily/weekly etc.

10. Test your Job works.

 

 

 

The following two tabs change content below.

Andy, Software Engineer

Andy is Software Engineer at Metaphorix