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.

Regards,
Saurav Dhyani
http://saurav-nav.blogspot.com/

Comments

Popular posts from this blog

VIEW SERVER STATE permission on SQL Server?

Hi all, Sometime While trying to Login into a database we face an error message as shown below. --------------------------- Microsoft Dynamics NAV Classic --------------------------- You cannot start Microsoft Dynamics NAV Classic because you do not have the VIEW SERVER STATE permission on SQL Server. Contact your system administrator. --------------------------- OK    ---------------------------

NAV 2013 R2 - Cumulative update 12 Released.

Hi all, Please find below the details of  Cumulative Update 12 released for Microsoft Dynamics NAV 2013 R2. Title - Cumulative Update 12 for Microsoft Dynamics NAV 2013 R2 Build No. - 38053 Release Date - October, 2014 Local Version Included - AU, AT, BE, CH, DE, DK, ES, FI, FR, IS, IT, NA, NL, NO, NZ, SE, UK, RU Download Link Note: Implementing this cumulative update will require a database conversion unless you have already implemented update rollup 5.

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    ---------------------------