It is possible and perfectly acceptable to put T-SQL commands into a scheduled task in order to create a full backup plan, but a Maintenance Plan is Microsoft’s way of allowing you the power of the GUI backup with the convenience of a scheduled task so that you don’t have to code your own.
Within SSMS expand Management, right click “Maintenance Plan”, and select “New Maintenance Plan”:
Name your plan (more sensibly than I have) and click OK:
This will present you with a Toolbox of actions in the bottom left of the screen and a designer in the bottom right. The top right is reserved for selecting your schedule, the plan name, and a description.
We’ll just create a very basic nightly full backup and notifying an Operator (if you have no Operators you can either skip that section or refer to the Operators article to learn how to set one up).
We’ll start with the schedule… click the blue button to the right of the schedule line:
This will open up a common schedule screen and allow you to select Daily at midnight:
For further information on schedules, please refer to the Schedule article.
Click OK and your schedule line should look like this:
Next, grab a “Back Up Database Task” and drag it into the designer…
Right click the newly created task and click “Edit…”. This will bring up a form similar to the main SSMS backup form:
Using the drop down lists and text boxes, select your database(s) and backup location then press OK.
This will now be reflected in the design screen:
Note: Backup compression is not present in SQL 2005 and there is a point of note discussed here
which you should read before applying the option.
To add an Operator, simply drag the Notify Operator task onto the designer:
To ensure that each task is performed sequentially and not at the same time, drag and connect the green arrow from the Backup task to the Operator task:
Right click and edit the Operator Task, the tick the relevant operator and complete the Subject and Message of the mail you wish them to receive:
Click OK and that’s it. You now have a nightly full backup which notifies you upon completion. Save the Plan (either by the save icon in the menu, or Ctrl+S) and you’re done.