Loading ...
Sorry, an error occurred while loading the content.

Re: [tampasql] copying maint plans from 1 server to another

Expand Messages
  • Garry McGibbon
    yes ... From: joseraulchinchilla@hotmail.com To: tampasql@yahoogroups.com Sent: Tuesday, August 31, 2010 11:00 PM Subject: Re: [tampasql] copying maint plans
    Message 1 of 9 , Sep 1, 2010
    • 0 Attachment
      ----- Original Message -----
      Sent: Tuesday, August 31, 2010 11:00 PM
      Subject: Re: [tampasql] copying maint plans from 1 server to another


      Great! Does that mean I get a free beer after next meeting?

      Sent on the Sprint® Now Network from my BlackBerry®

      From: "Garry McGibbon" <xroads9@...>
      Date: Tue, 31 Aug 2010 21:37:52 -0400
      Subject: Re: [tampasql] copying maint plans from 1 server to another


      Thanks for this info Jose.    You are dba of the year!    when I moved the maint plan the jobs got created automagically.    I found that by changing the connection properties in each task, that nothing was required at the job level. 
      ----- Original Message -----
      Sent: Sunday, August 29, 2010 11:49 AM
      Subject: RE: [tampasql] copying maint plans from 1 server to another


      Great answer! 



      From: tampasql@yahoogroups.com [mailto:tampasql@yahoogroups.com] On Behalf Of Jose Chinchilla
      Sent: Sunday, August 29, 2010 12:31 AM
      To: tampasql@yahoogroups.com
      Subject: RE: [tampasql] copying maint plans from 1 server to another


      I have succesfully renamed a SQL instance and made maintenance plans to work. Essentially you will have to modify the Connection properties under the Maintenance plan configurations. If there are too many maintenance plans to edit, you will probably be better off creating a SQL server alias so that it responds to the previous name.
      In terms of moving jobs you have some options since they are essentially XML based SSIS packages:
      1) Create an SSIS package and use the Transfer Jobs Task from the Control Flow task list
      2) Move the jobs manually and edit them in SSIS.
      This requires for you export the packages from the MSDB job repository to disk, add the packages to a SSIS solution, open each and create package configurations to replace connection strings and logins. This is a little more cumbersome but it may give you additional flexibility.
      3) Create a powershell script
      4) Scropt out the jobs using the related system stores procedures like sp_dts_putpackage, sp_add_job, sp_update_job,sp_add_jobschedule. Check this post for Matt Olson's example:

      My choice would be option 1. In terms of what you are looking Maintenance Plan + corresponding packages and steps, you will need a combination of option 1 and 4. Copy the packages over and then create the Maintenance Plan on destination server by generating script from source server and then add  steps, packages and schedules.
      I hope this helps.

       Jose R. Chinchilla


      To: tampasql@yahoogroups.com
      From: xroads9@...
      Date: Tue, 24 Aug 2010 22:16:52 -0400
      Subject: [tampasql] copying maint plans from 1 server to another


      I often use the sql server maintenance plans for backups,  integrity checks,  index rebuilds.   It's convenient, but what would be really convenient is if I could copy the plan from one server to another, and then just change a few things as necessary.   But this does not work;  somewhere the name of the machine is embedded in the plan, and moving it to another server won't work.  In fact, renaming the server will break existing maint plans.   Yet I can copy SSIS packages anywhere,  they do not have the same limitation.    Anyone know how to do this?

      sql2005 / 2008

    Your message has been successfully submitted and would be delivered to recipients shortly.