Good ol Maintenance plans. With 2005 and beyond we received the glorious SSIS backed Maintenance plans. I love SSIS so natually I love using these plans.

Last week I was working on setting up a new server.

We had to make sure we had full, differential, and transactional log backups. No big deal. I just created one maintenance plan with 3 subplans.

I went in and created all the steps that were required. Backup DBs, clean up old ones, and send mails based on pass/failure.

The manager of this project then wanted all the backups copied over to a different machine. I didn't need to do log shipping or mirroring or anything. The files just needed to be the same on both machines.

To do this I went into the SQL Agent and opened up the job for each of my steps. I created an extra powershell step that used robo copy to copy all of the backups after they were successfully created locally. Saved my jobs and everything was great.

About half a day later, the operator I set up to receive the pass/fail emails didn't like getting the transactional log backup emails every 30 minutes and wanted me to remove it from that subplan.

Meh - not a problemo. I went in and disabled that part of the maintenance plan. I saved my work and went on my merry way. A couple hours later, I checked to make sure everything was working correctly. I noticed that my robo copy job wasn't copying all the new backups. What the hell....

I went back into my jobs and noticed all of my powershell jobs were gone. Poof!

After some testing I found out that when you save a maintenance plan it overwrites whatever was there before. Gotcha!

So TIP 'o The Day: Do ALL your work for the maintenance plan INSIDE the maintenance plan.

My work around for this was to create another job just for the robo copy. I then added this job to each of the maintenance plans using the 'Execute SQL Server Agent Job Task'.