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/
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
Post a Comment