Automate Similar Backup Tasks on Multiple Instances
|
Scenario You need to schedule full, differential and t-log database backups on several servers and automate backup cleanup according to your company’s retention policy. |
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
- Select Maintenance Plans in the Navigation pane (CTRL+4).
- In the Server tree, right-click an instance and select Create New Maintenance Plan.
- Drag and drop the Back Up Database task in the middle of the Design pane and double-click it.
- Select the Full backup type.
-
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.
- Select the Create backup file for every database option and specify the Destination folder and file extension for the backups.
- 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.
- Click Ok to save the task.
- Click
on the tool bar. The task you just created will serve as a base for differential and t-log backups.
- Create two new subplans. To create a subplan, click
, then Ok.
- Select each of the two new subplans and click
to paste.
- 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.
-
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.
- Save the plan, click
Copy Plan.
- For all instances where you want to paste the plan, right-click the instance and select Paste Maintenance Plan.