Maintenance Plans in MS SQL 2005 are a powerful and easy way to manage and backup your databases. In this article, I’m going to walk through 4 basic tasks that we run weekly on a client’s database. You’ll learn how to reorganize indexes, create a full backup (*.bak), shrink your database (shrinks both data and log files), and cleanup the backup directory.

Creating a Maintenance Plan

In SQL Server Management Studio, expand the Management folder, right-click Maintenance Plans and select New Maintenance Plan…

Give the Maintenance Plan a name and click OK. The new empty Maintenance Plan will open in Design Mode. All of the available tasks are in the left hand column Toolbox under Maintenance Plan Tasks.

To add any task to the maintenance plan, simply drag the task from the Toolbox into the design frame. Once the task is in the design frame, double-click to edit.

Reorganize Index Task

The reorganize index task updates table and view indexes so the search order is more efficient. The task dialogue is pretty simple. Use the Database(s) drop down to select one or more databases. Then use the Object drop down to select whether Tables, Views, or Both should have their indexes reorganized. For this example, I’m going to reorganize both Tables and Views. When done, click OK.

Back Up Database Task

The Back Up Database Task creates a Full, Differential, or Transaction Log backup. In this example, I’m going to create a Full backup which will create a *.bak file.  This task dialogue is also pretty simple. Set the Backup type to Full and then select the Database(s) to backup. Jump down to the bottom third of the dialogue and select the Create a backup file for every database radio button. Then browse to the folder where the backup files are to be saved. When done, click OK.

Join the Reorganize Index Task to the Backup Database Task by dragging the green arrow between the two. Once the two tasks are joined, right-click the arrow and select Completion.

Shrink Database Task

The Shrink Database Task should come after the back up task because the shrink cannot be executed on a database until a Full backup has been created. In the task dialogue, select the Database(s) and just accept the defaults. When done, click OK. If you’ve noticed your log files getting out of hand, the shrink task will bring the file size down. In a recent test, my active log file went from over 2GB to just over 200MB.

Join the Back Up Database Task to the Shrink Database Task, and set the type to Completion.

Maintenance Cleanup Task

As the last task in the Maintenance Plan, I’m going to cleanup the backup directory by removing any *.bak files that are older than 2 weeks. If you’re doing this type of maintenance, you’ll want to make sure you’ve got some sort of server backup that keeps a longer history of your *.bak files off-server.

Open the task dialogue and skip down to the Search folder and delete files based on extension.  Navigate to the backup folder that was set in the Back Up Database Task. Then set the file extension to bak. Note the extension is simply bak. You don’t need to include a . (dot). Skip to the bottom of the dialogue and set the age of the deleted files to 2 weeks. When done, click OK.

Join the Shrink Database Task to the Maintenance Cleanup Task, and set the type to Success.

Execute the Maintenance Plan

Save the maintenance plan. From the Object Explorer, right click the new plan and select Execute to test it.  If everything runs without error, you can set the plan to run on a schedule.

On the design mode screen in the Subplan grid, click on the calendar icon and set the schedule for executing the plan. When done, click OK.

That’s it. The plan will now run as a scheduled task. Not only does the plan backup the database, but it’s also reorganizing the indexes, shrinking the database and cleaning up  the backup directory. Best of all, once it’s setup, the plan executes without your intervention and at a time that is best for your system.