Thursday, October 18, 2012

SQL Template to delete SQL job, maintenance plan and schedule

Problem: 
on our test servers , or servers that used to be managed by third party companies, the sql job list can become ...messy.
I came with a SQL server template to delete the job, the maintenance plan and the schedule if unused.

1. Create the template:

open the "view Template" window:

the template menu appears by default at the right of the SSMS window.


Create a folder of you choice in the Template explorer list , right click and choose "new template"
Name the template as you wish , right click the template and choose "Edit".
2. Now copy  the code that you can download from here

within the code you will notice special strings , these are the template parameters :


3.Save the template
4. Using the template:

Create a new query on the server on which you want to run the template.
Then simply drag the template you just saved on the blank query window
The template code is then copied on the new query window.
Then here is the cool stuff: click  on CTRL+SHIFT+M and you will be prompted to enter the parameters:

The first parameter  is to specify the job name, the second parameter (Delete_Maintenance_plan) should be  set to 1 if you want to delete the associated maintenance plan. Of course this is only a simple script, error check should be added to check if there is actually a maintenance plan etc..

Nonetheless it is still useful, and shows the basics of using templates.


No comments:

Post a Comment