When you create a SQL Server Maintenance Plan using the Maintenance Plan Wizard or the Maintenance Plan Designer, the SQL Server native backup command is used to perform full, differential, and transaction log backups. But what if you want to use backup compression to reduce the size of your backups and the amount of time it takes to perform them? Or what if you want to encrypt your backups to protect your organization’s data, or make the transfer of backup files across networks fault-tolerant? If you have the SQL Server 2008 Enterprise Edition, backup compression is built in, but if you have any other version or edition of SQL Server, or if you want encryption or network resilience, then you will need a third-party backup program, such as SQL Backup from Red Gate Software.

The Maintenance Plan Wizard and the Maintenance Plan Designer don’t have the ability to automatically include third-party backup software as part of a Maintenance Plan. Fortunately, there is an easy way around this, and in this article I will show you how you can use SQL Backup within a Maintenance Plan.

First, I am going to show you how to manually create a new Maintenance Plan that uses SQL Backup instead of the native SQL Server backup command. Second, I’ll tell you how to manually convert an existing Maintenance Plan to use SQL Backup. And third, I’ll show you how to automatically convert an existing Maintenance Plan to use SQL Backup.

Note: If you are not familiar with how to use the SQL Server Maintenance Plan Wizard or the Maintenance Plan Designer, check out my free eBook, Brad’s Sure Guide to SQL Server Maintenance Plans. This article assumes you already know the basics.

How to Manually Create a New Maintenance Plan that Uses SQL Backup

While there are several different ways to get SQL Backup to work within a Maintenance Plan, I’m going to start by showing you how to do it from scratch. This is because the information you learn in this section is directly applicable to the following two sections. Once you understand this section, understanding the remaining two sections will be easy.

As you know, a Maintenance Plan can be created using either the Maintenance Plan Wizard or the Maintenance Plan Designer. While it is feasible to use the Maintenance Plan Wizard to incorporate SQL Backup into a Maintenance Plan, it is actually easier to use the Maintenance Plan Designer. Because of this, I’m not going to bother showing you how to use the Maintenance Plan Wizard to include SQL Backup. Instead, I am going to take the easy route, and show you how to include SQL Backup in a new Maintenance Plan created with the Maintenance Plan Designer.

To illustrate to you how to do this, I am going to lead you through the process, step-by-step, using the following example, with the following assumptions.

• We are going to create a new Maintenance Plan using the Maintenance Plan Designer. In order to stay focused, this Maintenance Plan will only include backup tasks. Other maintenance tasks have been left out for simplicity. Of course, you can use any of the available maintenance tasks in your Maintenance Plan, and they will not affect how SQL Backup works.
• Again, to keep the example simple, we will only back up the AdventureWorks database. Of course, you can back up any databases you want in your own Maintenance Plans.
• We will perform a full backup daily at 1:00 AM, and we will also perform hourly transaction log backups. Full backups will be added to one subplan and schedule, and transaction log backups will be added to a second subplan and schedule.
• When we configure SQL Backup for use by our Maintenance Plan, all default options will be used. When you create your own Maintenance Plans, you can choose any options that best meet your needs.
• We will be using the “Execute T-SQL Statement Task” of the Maintenance Plan Designer to include the appropriate T-SQL code necessary to run SQL Backup. If you are not familiar with this task, it is simply designed to run any T-SQL code you want to within a Maintenance Plan.

These simple assumptions will make it easier to explain the steps you need to include SQL Backup within any Maintenance Plan you create with the Maintenance Plan Designer, without going into a lot of unnecessary detail. Once you understand the overall process, you will be able to adapt what you have learned to meet your specific needs.

The first step is to open the Maintenance Plan Designer. To do this in SQL Server 2005/2008 SSMS, open up the “Management” folder, then right-click on the “Maintenance Plans” folder, and the following pop-up box appears.

Select “New Maintenance Plan”, and the following screen appears.

Enter the name of the new Maintenance Plan and click OK, and the Maintenance Plan Designer screen appears.

Now that the Maintenance Plan Designer has been started, we need to create two subplans. The first one will be for the daily, full backup, and the second will be for the hourly transaction log backups. By default, one subplan, called Subplan_1 has been created when you start a new Maintenance Plan. To make the Maintenance Plan easier to understand, let’s change the default name of Subplan_1 to “Daily Full Backup.” To do this, double-click on Subplan_1, and the following box appears.

Above, you can see that the subplan has been renamed, and a brief description has been added for self-documentation purposes. For the moment, we will skip scheduling the subplan, saving this task for one of our last steps. Once the name and description have been changed, click OK, and you are returned to the Maintenance Plan Designer, which now shows us the new subplan name and description.

Next, we need to create a new subplan for the transaction log backup. To do this, click on the “Add Subplan” icon at the top of the screen, and the following screen appears.

To create this second subplan, enter a name and description (see above), and then click OK, and the Maintenance Plan Designer screen appears, now with two subplans (see below).

At this point, we have our two subplans. Our next goal is to configure each subplan so that SQL Backup is used to perform full and transaction log backups. We need to do this one subplan at a time. Let’s start with the Daily Full Backup subplan.

To do this, click on the Daily Full Backup subplan (see below) so that we are working on its designer surface. Next, drag the “Execute T-SQL Statement Task” from the Toolbox to the designer surface. The designer surface now shows the “Execute T-SQL Statement Task”, as shown below. Notice that the task has a round, red circle with a white “x” in it. It is a little hard to see in the screenshot. For those of you who are not very familiar with the Maintenance Plan Designer, this means that this task has yet to be configured. We will configure it shortly.

Now, we need to do the same for the Hourly Transaction Log Backup subplan (see below). Click on the subplan, then drag the “Execute T-SQL Statement Task” from the Toolbox to its designer surface, as you see below.

Now that we have created two subplans, and put the “Execute T-SQL Statement Task” on each of the subplan designer surfaces, we are ready to configure each task. But before we can do this, we need to identify the T-SQL code that we need to execute SQL Backup so that it performs the full and transaction log backups that we want.

One option would be to write the T-SQL code from scratch. SQL Backup’s documentation explains how to do this. Or, we can get SQL Backup to write the T-SQL code for us. Since it’s much easier for SQL Backup to do all the hard work for us, let’s find out how to accomplish this task.

Note: You can leave the Maintenance Plan Designer running while we get the T-SQL code from SQL Backup. Once we get the code, we will then cut and paste it into the appropriate tasks within the Maintenance Plan Designer.

To get SQL Backup to write the T-SQL code we need, start SQL Backup. Below is a partial screenshot of the application.

To get SQL Backup to write the T-SQL code, we need to start the Back Up Wizard, just as if we were going to create a backup directly from the SQL Backup GUI. To do this, click on “Back Up”, and the wizard starts, as shown below.

As I mentioned earlier, in our assumptions, we are going to accept all of the defaults for SQL Backup. Of course, you can choose any options you want when you create your own Maintenance Plan. Since the AdventureWorks database is located on the Hawaii server, click Next.

In step 2 of the wizard (see above), the backup type selected is “Full”, and the AdventureWorks database has been selected. Click Next.

In step 3 of the Wizard (see above), we are accepting all of the defaults, so click Next.

As with step 3, we are going to accept all of the defaults in screen 4 (see above). Click Next.

We have now reached screen 5 of the wizard, the last screen, and we are done. All we need to do now is to check out what T-SQL code SQL Backup has created for us. Click on the Script tab (see above) to see the code.

As you can see above, SQL Backup has written the T-SQL code we need to add to our Maintenance Plan so that full backups are performed. At this point, we are now ready to cut and paste this code into the “Execute T-SQL Statement Task” on the “Daily Full Backup” subplan. To cut the T-SQL code, select it from the screen above, and then press Control-C on your keyboard.

Note: You may want to include error checking for the T-SQL code generated by SQL Backup. To learn more about how to do this, visit this webpage.

To paste this T-SQL into the Maintenance Plan Designer, let’s go back to it and take a look at it.

In order to add the T-SQL code we just cut from SQL Backup, we need to right-click on the “Execute T-SQL Statement Task” box on the “Daily Full Backup” designer surface above, and select Edit. When we do, the following screen appears.

As you can see, the T-SQL code has been pasted into this screen by pressing Control-V, although we can see it all because the code extends to the right of the screen. At this time, we are done with this task. Everything else on the screen can remain at their default settings. So click OK to save the T-SQL Code, and we are returned to the designer surface (see below).

If you look closely at the “Execute T-SQL Statement Task” on the designer surface, you will notice that it has changed slightly, and that the round, red circle with the white “x” in it is now gone. This tells us that this task has been configured.

That seemed like a lot of work, but it really wasn’t. While it might have taken you several minutes to read through these steps, it took less than a minute when I did it on my computer. Why am I telling you this? Because we now need to go through all of the same steps we just did for the “Execute T-SQL Statement Task” for the “Hourly Transaction Log Backup” subplan. In other words, we need to click on the “Hourly Transaction Log Backup” subplan and add the T-SQL code for transaction log backups to the “Execute T-SQL Statement Task”. And just like we had SQL Backup create the T-SQL for us, we can use the same Back Up wizard from within SQL Backup to create the code. The only difference between the previous example, and now, is that we choose “Transaction Log” as the “Backup Type” instead of “Full.” All the other steps are the same. Because all of the steps are the same, I’m not going to repeat them here.

Once the SQL Backup T-SQL code has been added to the “Execute T-SQL Statement Task” for both the “Daily Full Backup” and “Hourly Transaction Log Backup” subplans, it is now time to schedule them. While we could have scheduled them at any point when creating this Maintenance Plan, I have saved it for last just to keep this explanation as simple as possible.

To schedule the “Daily Full Backup” subplan, click on the schedule icon next to the subplan (it looks like a tiny calendar in the above screen shot), and the following screen appears.

If you have used the Maintenance Plan Wizard or Designer before, the above screen will look familiar. It has already been configured so that the “Daily Full Backup” subplan will run daily at 1:00 AM. Once you are done configuring the schedule, click OK.

Next, we create the schedule for the “Hourly Transaction Log Backup” subplan. Click on the schedule subplan next to it, and the following scheduling screen reappears.

Above, the scheduling screen has been configured so that the “Hourly Transaction Log Backup” subplan will run once an hour. When you are done configuring the schedule, click OK. When you are returned to the Maintenance Plan Designer (see below), you can see that the schedules have been saved.

At this point, we are now done with our simple SQL Backup Maintenance Plan, and it is configured to begin running on the schedule that was just created.

To summarize, the easiest way to add SQL Backup to a new Maintenance Plan is to add the appropriate T-SQL code (which SQL Backup can create for you automatically) to the “Execute T-SQL Statement Task”, and include it as part of any Maintenance Plan you want to create.

How to Manually Convert an Existing Maintenance Plan to Use SQL Backup

While the previous (long) section showed you how to create a new Maintenance Plan that incorporates SQL Backup, what if you already have an existing Maintenance Plan, and instead of starting from scratch, you want to modify it to use SQL Backup. This is no problem at all. Essentially, all you have to do is to open your existing Maintenance Plan with the Maintenance Plan Designer, remove the “Back Up Database Tasks” included in your plan that use the native SQL Server backup, and then add the necessary “Execute T-SQL Statement Tasks” that contain the appropriate T-SQL code to execute SQL Backup, as described in the previous section. Essentially, all you are doing is swapping out the native backup tasks for the SQL Backup tasks.

How to Automatically Convert an Existing Maintenance Plan to Use SQL Backup

Besides manually converting an existing Maintenance Plan to use SQL Backup, you also have the option to have SQL Backup do the conversion for you. You may not be aware of this feature, but SQL Backup includes a tool called the Maintenance Plan Conversion Wizard. This tool can take an existing Maintenance Plan that uses the native SQL Server backup, disable the old “Backup Up Database Tasks” and replace them with the “Execute T-SQL Statement Tasks” that include the appropriate T-SQL code to run SQL Backup.

Let’s take a quick look at how the SQL Backup Maintenance Plan Conversion Wizard works. Let’s say that we have a very simple Maintenance Plan that performs a full backup of the AdventureWorks database every day at 1:00 AM. If the backup task succeeds, then no message is sent, but if the backup task fails, then the “Notify Operator Task” should be fired, notifying a DBA that the backup failed. Below you see such a plan from within the Maintenance Plan Designer.

Note: If you are not familiar with precedence or the “Notify Operator Task”, get a free copy of my book, Brad’s Sure Guide to SQL Server Maintenance Plans.

Now, let’s find out how to convert this very simple Maintenance Plan to one that uses SQL Server Backup instead of the SQL Server native backup.

The first step is to start the SQL Backup Maintenance Plan Conversion Wizard by selecting Tools|Utilities|Maintenance Plan Conversion Wizard from the SQL Backup main menu, as shown below.

Once the Wizard begins, the following screen appears.

The first step is to select the SQL Server instance where the Maintenance Plan to be converted is located, and to choose how you would like to connect. You must be a member of the sysadmin group in order to use the Wizard. Click Next to continue.

The above screen of the Wizard only displays those Maintenance Plans that have a “Back Up Database Task” in them, otherwise they won’t be displayed. While you can select multiple Maintenance Plans to convert at the same time, I suggest you do only one at a time, to prevent any potential confusion. Once you have selected the Maintenance Plan you want to convert, click Next.

Note: If your Maintenance Plan has more than one “Back Up Database Task” (e.g. to create full and transaction log backups), you will see one additional Wizard screen (not shown here), asking if you want to use the same SQL Backup options for each of the tasks. If you select yes, you will see only one set of default option screens. If you choose no, then you will be asked to select separate options for each backup task.

This above screen allows you to select from some of the SQL Backup options. I highly recommend that you select the “Delete backup files older than” option, as the “Maintenance Cleanup Task” does not have the ability to remove backups created with SQL Backup. Once you have made your choices, select Next.

The above screen of the Wizard presents even more SQL Backup configuration options. Make your selections and click Next.

We are now done with our options (see above) and are ready to start the conversion. Click Start to begin the conversion process.

Once the conversion is complete, click Finish to exit the SQL Backup Maintenance Conversion Wizard.

Now, let’s take a look at what the Wizard did to our sample Maintenance Plan (see below).

What the Wizard did was to disable the “Backup Database Task” (it is now grayed out) and create a new “Execute T-SQL Statement Task” that has been renamed “SQL Backup – Backup Database Task”. Notice also that the precedence used with the “Notify Operator Task” is still intact.

If you open up the “SQL Backup – Backup Database Task” and look at the T-SQL Code in it, it looks like this (see below).

While some of the T-SQL code is truncated, what I want to point out is that besides the SQL Backup command, the Wizard also created some additional error-handling code, which can help when trying to troubleshoot backup problems.

Whenever you use the SQL Backup Maintenance Plan Conversion Wizard to convert SQL Server Maintenance Plans, it is always a good idea to review any changes made by the Wizard after the conversion, and then to monitor the Maintenance Plan to verify that it is running as you expect. If you run into any problems, you should be able to easily go back into the Maintenance Plan Designer and make any necessary changes. Once you have determined that everything is running smoothly, then you can leave your Maintenance Plan, and SQL Backup, to the routine task of making your backups.

Summary

In this article you have learned that it is an easy task to incorporate SQL Backup into any SQL Server Maintenance Plan. You have the choice of creating a new Maintenance Plan using the Maintenance Plan Designer, manually modifying an existing Maintenance Plan using the Maintenance Plan Designer, or you can have SQL Backup do the conversions for you automatically, using the SQL Backup Maintenance Plan Conversion Wizard.