LiteSpeed for SQL Server 8.9.8 - Security and Compliance Guide

Automate Similar Backup Tasks on Multiple Instances

Most of the customer maintenance plans I have seen will contain a backup task in each of one or more sub plans. These sub plans will divide the maintenance plans into performing either a full backup, diff backup or TLOG backup, one backup type per sub plan, each with its own reoccurring schedule. They also will have one maintenance plan for user databases and another for system databases.

You are going to create a maintenance plan on one SQL Server instance and then simply copy it to the other server instances.

To automate backups on server instances

1. Select Maintenance Plans in the Navigation pane (CTRL+4).
2. In the Server tree, right-click an instance and select Create New Maintenance Plan.
3. Drag and drop the Back Up Database task in the middle of the Design pane and double-click it.
   1. Select the Full backup type.

   2. Click and select User databases (excluding master, model, msdb).

      NOTE: This scenario describes how to back up all user databases. Similarly, you can configure maintenance plans to back up system databases and databases matching wildcard or regular expressions.

   1. Select the Create backup file for every database option and specify the Destination folder and file extension for the backups.
   2. Select the Options tab and select the Remove files older than option. Specify when the full backups are eligible for cleanup according to your company’s retention policy.
   3. Click Ok to save the task.
4. Click on the tool bar. The task you just created will serve as a base for differential and t-log backups. It is copied to other subplans in the plan so that every task executes with its own reoccurring schedule.
5. Create two new subplans. To create a subplan, click , then Ok.
6. Select each of the two new subplans and click to paste.
7. Configure the copied subplan tasks to create the differential and t-log backups instead of full backups. Specify when the differential and t-log backups are eligible for cleanup according to your company’s retention policy.

8. Double-click every subplan in the plan. Enter the name and description. Click to set schedule properties. Each subplan should have its own reoccurring schedule. For example,

   • Full backups occurring every week
   • Differential backups occurring every day
   • T-log backups occurring every 20 minutes

   When you are done editing the subplan properties, select Enabled and click Ok.

9. Save the plan, click Copy Plan.
10. For all instances where you want to paste the plan, right-click the instance and select Paste Maintenance Plan.
11. Create a new maintenance plan, open it in the Design pane and click . Repeat for all instances where you want to paste the plan.

Related Topics

About Automating Maintenance Tasks

About Creating Maintenance Plans

Back Up Databases Using Maintenance Plans

Copy Maintenance Plans