Maintenance Plan Conversion Wizard

From RedGateWiki

1 Introduction
2 Conversion Walkthrough

Introduction

The Maintenance Plan Conversion Wizard is a free utility for converting existing Maintenance Plans created with SQL Server 2000 and 2005 to utilize the functionality of SQL Backup.

The Maintenance Plan Conversion Wizard can be downloaded from http://downloads.red-gate.com/labs/SQBMaintPlanConv.zip.

Please post any questions and feedback on the forum at http://www.red-gate.com/messageboard/viewforum.php?f=73.

[edit]

Conversion Walkthrough

[edit]

The conversion process

The conversion process breaks down into 6 steps:

Identify the SQL Server instance which contains the plans to convert
Identify one or more plans to convert
Configure any settings for new functionality provided by SQL Backup - for example compression and encryption settings
Review the changes
Apply the changes
Verify the changes

[edit]

Identify the SQL Server instance

The first step of the wizard requests the name of the SQL Server instance that contains the maintenance plans to convert, it will also request the type of authentication required to connect to the instance.

The "..." button will display a list of SQL Server instances on the local network.

Image 1: Server Selection

[edit]

Identify one or more plans to convert

The second step is to select one or more plans to convert. The wizard can convert both SQL Server 2000- and SQL Server 2005-generated maintenance plans, including legacy plans transferred from SQL Server 2000 to SQL Server 2005.

A list of plans will be displayed based on the following criteria:

The SQL Server agent job(s) associated with the maintenance plan are enabled;
The maintenance plan contains at least one valid native backup step (and if in SQL Server 2005, this step must be enabled);
- If in SQL Server 2005, this step must be enabled;
- A step is deemed to be "valid" if it contains at least one referenced database. Invalid or "incomplete" steps in SQL Server 2005 may refer to zero databases.

If the plan does not appear in the list, this could be because the plan has already been converted, or because it does not contain any convertible steps.

Image 2: Maintenance Plan Selection

If a backup plan contains more than one backup type (for example, a full and a differential step), the wizard will ask if all steps should be treated the same, or if different backup types should use different options.

Image 3: Multiple Backup Types selected

[edit]

Configure any settings for new functionality provided by SQL Backup

Because SQL Backup provides a variety of new features over what is available with native backups, some configuration options must be supplied to use these new features.

There are two required settings:

Backup Folder - the name of the folder to back up to. This can optionally use the <tags> available in SQL Backup (see http://help.red-gate.com/help/SQLBackup5/2/en/SQLBackup.htm#topics/SBU_FileLocationTags.html for more information);
File name - the name of the file to back up to, this requires the use of the <tags>.

Six other settings are also available:

Overwrite existing files - if a file with the same name already exists, it will be replaced with the new backup.
Delete backup files older than ... - will delete any matching backups (that is, the same server, database and backup type) that are older than the timeframe specified.
Verify files after completion of backup - performs some additional checks on the file once it has been created to confirm that the backup has not been damaged or corrupted.
Compress backup - use compression to shrink the size of the backup file. Higher compression levels will generally product smaller files, but may take slightly longer to produce.¹
Encryption options - use 128-bit or 256-bit encryption to secure the backup file from unauthorized users.¹
Email completion log - will send an email either when the backup is completed, or if an error occurs (using the email settings configured in the SQL Backup 5 GUI).¹

¹ - subject to SQL Backup license - see http://www.red-gate.com/products/SQL_Backup/feature_comparison.htm for details of the SQL Backup versions available.

Image 4: Part 1 of the available settings

Image 5: Part 2 of the available settings

[edit]

Review the changes

After the settings have been supplied, the wizard will provide a summary of the steps it will be performing. If you are happy with these, click "Start", otherwise click "Back".

The wizard will then indicate the progress that has been made, including raising any issues that may have occurred when converting the Maintenance Plans.

Image 6: Review Changes

[edit]

Verify the changes

Once the plan has been converted, it is recommended that the maintenance plan is checked to ensure it matches the expected outcome.

In SQL Server 2000 and legacy plans, a new plan and SQL Server Agent job will be created with the changes, and the old plan's SQL Server Agent job will be disabled.

In SQL Server 2005, the existing plan will be modified, disabling the steps involving native backups, and replacing them with steps involving SQL Backup. Any links or relationships between job steps should remain intact.

Image 7: Conversion Complete