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'.