Configuring SQL Server Maintenance Plan

I may not have explicitly mentioned earlier, but all the examples, and the content that I write generally applies to SQL Server 2008. In most cases it will be true for SQL Server 2005 as well, but not always. For example, in one of the blog about compressing data during backup, I discussed "WITH COMPRESSION" command to use during backup. This command was only introduced with SQL Server 2008.

Today, we will review another facility available in SQL 2008 (also available in SQL 2005, but some of the features may not be the same) called Maintenance Plan. Maintenance Plan allows you to setup certain maintenance tasks by using SQL Management Studio with ease. This comes specially handy when you have multiple databases on the same server and need to setup maintenance plan such as nightly backup for all the databases at once.

1. Connect to your SQL instance via Management Studio and go to Management > Maintenance Plans.

2. Right click on Maintenance Plan and either select New Maintenance Plan or Maintenance Plan Wizard. Maintenance Plan Wizard is quite powerful and walks you through selecting available maintenance plans and configure it. In this example, we will use the first option and setup our own maintenance plan. Select "New Maintenance Plan" and name your plan appropriately, which will open design surface and a list of available maintenance plan.

3. Drag one or more tasks that you want to configure in this maintenance plan. We will configure two tasks - database backup and check database integrity task.

Check Database Integrity

It is often a good idea to check the database integrity immediately before or after the backup.

Drag the task on the design surface and then right click > Edit to configure your databases. The resulting UI will allow you to select one or more databases. You can also check all databases (this is helpful if you are going to add databases in future and don't want to keep adding new databases to this list, alternatively if you have too many databases, you may want to configure few databases in one task).

Check the "Ignore databases where state is not online" to only perform integrity check when database is online. Click on OK to save the change. 

From the design surface top menu, click on the calendar icon to setup the schedule for this task. Name your schedule and setup appropriate schedule. Clock on OK to save and close the schedule window. 

Configure Database Backup

Repeat the same steps and drag the database backup task and configure it. Again right click on the task and configure your options. Here you can configure whether you want full, differential or transaction log backup, select one or more databases and define the location where you want to save the backup files. There is also an option called "Verify Backup Integrity" this shouldn't be confused with Database Integrity task which we configure in above step.

In this example, we want to ensure the integrity check is performed first before the database backup. Right click on either of the two task and select "Add Precedence Constraint" from the context menu and select the precedence you want.

That's all there is to it. Once you save your plan, SQL Server will automatically create a SQL Agent job to run your plan at the scheduled time.

If I had database mail setup, I can also add a third task "Notify Operator Task" as the last step which can send a notification email in the event of the task failure. See my previous post to learn about configure your SQL Server to send emails.

Thank you.