March 10, 2009 at 10:37 am
Hi all.
I'm running SQLserver 2005 with SP2 and have maintenance plans to backup all databases.
I have added an extra step to the job executing the plan to copy the backup files to a SAN using a batch file after the local backup has finished.
After changing the plan to backup only selected databases, I have lost my extra step in the job.
Microsoft says that's the correct way after SP2 because after reconfiguring the plan, the job is re-created.
OK, I agree. But... Is there any way to integrate a cmdexec in a plan to avoid loosing the extra job steps?
Sometimes I have to uncheck some databases to avoid "disk full" issues when backing up test databases, but I don't like to re-do the job after every change.
In 2000 we had only a warning but the job still working after modifiying the plan!
March 10, 2009 at 11:41 am
Create the maintenance plan, but don't schedule it in the plan (if you know what I mean). Instead, create your own job that calls the maintenance plan, and also calls your batch file. That should work, but I have to admit I haven't tried it.
You can also create a maintenance plan as an SSIS package, and have that in a job.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 11, 2009 at 4:17 am
Thank you for the suggestion GSquared.:)
I´ll try the independent job an let you know if it works.
As for the SSIS package, is all new for me (never needed to program!!!). Can you give me some hint to open that door?:Whistling:
March 11, 2009 at 6:53 am
If you open up the Business Intelligence Design Studio (BIDS), you'll see Integration Services as an option. Start a new one, and take a look at the options. You can build maintenance plans in there, and then some. It's a pretty cool tool.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 11, 2009 at 7:36 am
Please check this for detailed
http://www.microsoft.com/sqlserver/2005/en/us/integration-services.aspx
March 11, 2009 at 12:13 pm
😉 I DID IT!!!
Thank you all for your feedback.
The final solution is a little tricky but it works.
Opened the SSIS console and looks a good toy, but I have had an error during the first try and I'm so old-fashioned that my last "real" program was wrote in Clipper summer '87. Maybe I'll try it later.
Anyway...
Looking at the properties of the original job, realized that it was treating the maintenance plan as an SSIS package so, why to write a SSIS package if I have it yet?
I have removed the scheduling from the maintenance plan, disabled the original job (can't delete it because a foreign key) and created my own job configuring the first step as an SSIS package calling the maintenance plan, and the second step as a cmdexec calling a batchfile that can do for example a compression of the files and a robocopy to the final storage place.
Launched the new job and it ran without errors but suspiciously fast.
No local nor remote new backup files!!!
After a closeup to the original job found the problem. A undefined variable error. we must define a value and put it to "false" as you can see in the attached image.
It worked after two changes in the database list of the maintenance plan and I have left it to test the schedulling in a noncritical server.
I hope that can help others facing similar problems.:cool:
March 12, 2009 at 6:47 am
Glad we could help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply