How to transfer Maint Plans (chkdb,reorg & so on) from one server to other

Question

Post reply

How to transfer Maint Plans (chkdb,reorg & so on) from one server to other

SQLRocker

SSCrazy

Points: 2217
More actions
November 5, 2009 at 2:50 pm

#211933

BIDS Tx. Jobs task doesn't work as maint plans don't get tx. to the target MSDB db. I have had issues in importing packages to the target server, Is there any other way, I have got 4 servers where I need to tx. them to, & thought if there is a easier way.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

MANU-J.

SSC-Dedicated

Points: 31126

November 6, 2009 at 2:02 pm

#1076134

Any solution or trick for this?

MJ

josantia Ten Centuries Points: 1245 More actions · Answer 2

Hi,

If you have installed Integration Services on the server, you can connect to the instance and export them and the import them in the other server.

Regards.

Jose Santiago Oyervides.

SQLRocker SSCrazy Points: 2217 More actions · Answer 3

Hi Jose,

http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/567ca99e-3627-4787-b54b-413ba5ce278c

Link for the issues I am having with IS export.

Jeffrey Williams SSC Guru Points: 90250 More actions · Answer 4

Here is the process I use:

1) Create maintenance plan in SSMS as template. Create all sub-plans, tasks, etc...

2) Open BIDS and create new SSIS package

3) Add Existing Items to new package - import from SQL Server where I created the SSMS template.

4) Once the packages have been imported into BIDS - then we can modify certain aspects of the plans. Note - making further changes can cause issues.

5) Change Local Connection to point to target server (DO NOT CHANGE THE NAME - only change the properties to the point to the target server).

6) Modify each task that needs to be modified - for example, backup task - modify destination folder if needed, cleanup task - modify destination folder, etc...). Note: do not try to add tasks or remove tasks, this can be problematic.

7) Save Copy As - select target server, specify maintenance plan folder and give it a name. If there is already a plan with that name you need to delete the previous plan first.

This works as long as you are saving the plans to a SP2 or greater instance (actually, the minimum should be 9.0.3054, anything less than that will cause problems).

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

SQLRocker SSCrazy Points: 2217 More actions · Answer 5

I can try the above process next time, step 3 is still the issue tho, can't export pkg coz of the error, anyone knows what the error is about, for now I have manually create maint plans on all servers.

SQLRocker SSCrazy Points: 2217 More actions · Answer 6

I will try this next time, think will work:

1. Use GUI or dtutil to copy all packages from one server to other.

dtutil /COPY DTS;"msdb\maintenance plans\testdtutil" /DTS "msdb\maintenance plans\testdtutil" /SOURCESERVER server1 /DESTSERVER server2

2. Use BIDS tx. jobs utility to tx. the jobs.

3. Check & modify jobs on destination as necessary.

SQLRocker SSCrazy Points: 2217 More actions · Answer 7

I tried the above, tx. maint plans & tx. jobs, Modified the jobs - gave error while executing, Modified maint plans - gave error, better just to create new maint plans.