Skip to main content

SQL Maintenance Plan with Database Mail.

Hi all,

As discussed in last two post about

1) How to Configure Database Mail in SQL Server.
2) How to create operator in SQL?

Let's see the uses of last post in this post, by creating a simple maintenance plan for taking automatic Backup from SQL and Notifying the administrator about success or failure of the Job performed by Maintenance Plan.

There are many Default maintenance Task available in SQL, but for demo we will be using Complete Backup Maintenance Task.

For Running a Maintenance Plan automatically on specific schedule we require to start SQL Server Agent as shown below.

But the SQL Server Agent is set to start manually in services console, so let's change the property of service too as shown below.

As our service properties are set, we are ready to start the DEMO.

1) Open the Management Folder, and Right click Maintenance Plan and select Maintenance Plan wizard.

2) Provide a Name and Description to Maintenance Plan. By Default a maintenance plan is scheduled to to
    run on demand so let's change the schedule too as shown below.

3) As shown above i want to run my maintenance plan daily at 9:00 PM Starting Today.

4) Click ok and go to next window. Here you will find a list of Maintenance Task that are available by default with SQL.

5) As per demo purpose i will select Backup Database (Full). In the window below you will find the description of the Task as shown below.

6) In the Next Window Select the Database, Folder where you want to store the Backup and click Next as shown below.

7) In Next Window you can specify the path the execution log of the job should be kept and select E-mail Operator whom you want to send the Email (as discussed in last post).

8) In Next window click finish and job will get created successfully.

9) Now you will find the Maintenance Plan Under Maintenance Plan Folder. Double Click the Maintenance Plan created. A window will appear as shown below.

10) From the Left hand panel double click the Notify operator Task twice. Link Main job with both the Notify Operators.

11) Set the properties of the lines, Green Represent Success and Red Represents Failure as shown below.

12) Now edit these Notify operator Tasks as shown below with Email contents as shown below.

13) Now close and save the maintenance plan.

As we have successfully created the maintenance plan, lets test it. Right click the maintenance plan and Select Execute. A confirmation window will appear once job completes as shown below.

Here are the other outputs of job- 1) Email confirmation. 2) Database Backup File. 3) Database Log.

I hope this will help someone. Hope you like the post.

Saurav Dhyani


Popular posts from this blog

Send Mail with Attachment From Navision.

Hi all, We have seen how to save a report into PDF and how to send mail to a customer. Let's link these two post in one i.e. Mailing statement to a customer into PDF Format. This article is part of the Series. Please Refer  Table of Content here . If you have the old objects set let me brief you what I will be changing - 

Dynamics 365 Business Central 2021 release wave 2 (BC19) Released.

Hi Readers, As we all were expecting Microsoft have Released Business Central 2021 Release Wave 2 Today a.k.a BC19. In this article we will see what all is announced with the General Availability.  Starting Today Customers & Partners can start using Business Central 19. #msdyn365bc #bc19

Error After Restore SQL Backup of NAV 2013 Database

Hi all, we are facing a conman issue during restoring a SQL Database backup restored for NAV 2013. While Trying to run object from Object Designer we get below listed error (even the service is configured properly) - --------------------------- Microsoft Dynamics NAV Development Environment --------------------------- There are no NAV Server instances available for this database. You must ensure that a NAV Server instance is running and is configured to use the database before you perform this activity. --------------------------- OK    ---------------------------