January 5, 2009 at 4:59 pm
Hi,
we are upgrading from sql server 2000 to 2005. In this we have the jobs which are created in sql server 2000 through maintenance plans.
1.
In 2005 do we need create the maintenance plans exactly as in 2000 with out scripting the jobs in 2000 and run in 2005
or
script jobs in 2000 and ran it in 2005 and create the maintenance plans in 2005 as in 2000?
please suggest me
January 5, 2009 at 6:20 pm
I think scripting is better, but I'm not sure if you can have any results with Upgrade advisor!? - Try just in case! The other maybe have much info about that!
January 6, 2009 at 10:09 am
This article http://www.dbazine.com/sql/sql-articles/cook10 tells how to script SQL 2000 maintenace plans by scripting the jobs that run them. I haven't tried it so I can't vouch for the method and I don't know if it works to run the script in SQL 2005.
SQL 2005 maintenance plans, unlike those in SQL 2000, are SSIS packages. If you can get the legacy maintenance plans created in a SQL 2005 instance, you can run a migration wizard to migrate them to SQL 2005 plans. The trick seems to be getting the legacy plans into SQL 2005.
Greg
January 6, 2009 at 10:29 am
thanks,
when we create maintenance plans, the related jobs were created in sql server 2000 under jobs. If we script these jobs and run in sql server 2005, the jobs will be created.
In this case my question is do we require to create the maintenance plans in 2005 (as in 2000) or not?
If we create the maintenance plans in 2005 again, it will create the related jobs automatically right? then there will be duplicate jobs created as we previously we ran the job script from 2000?
please clarify me
January 6, 2009 at 10:59 am
Is this an in-place upgrade? If so make sure you opt to include the backward compatibility options, then you will get a 'legacy' section in SSMS which will include all your maintenance plans and the jobs that go with them.
One of the best advantages of an in-place upgrade is the contents of msdb is maintained.
I would then still recreate the maintenance plans in 2005 (from scratch) over time.
If you are doing a side by side upgrade I would simply recreate the maintenance plans from scratch, the reason for this being that jobs that are created from maintenance plans in SQL 2000 use the sqlmaint utility which is deprecated in 2005. You can still script out any other SQL jobs though and this is the best way to migrate them.
---------------------------------------------------------------------
January 6, 2009 at 11:06 am
We are doing side-by-side upgrade. In sql server 2005 also when we create maintenance plan,the related job will be created under jobs section right? So if we ran the job script from 2000 and create maintenance plans from scratch will lead to duplicate jobs...
January 6, 2009 at 11:27 am
Mani (1/6/2009)
We are doing side-by-side upgrade. In sql server 2005 also when we create maintenance plan,the related job will be created under jobs section right? So if we ran the job script from 2000 and create maintenance plans from scratch will lead to duplicate jobs...
yes when you create the maintenance plan it will create the jobs as well, so if you load the old jobs from a script you will effectively have duplicates, so basically don't script out maintenance jobs, start from scratch, its worth the effort.
---------------------------------------------------------------------
January 6, 2009 at 11:39 am
thank you,
So other than jobs created by maintenance plans, we can script out and and ran in sql server 2005 right?
January 6, 2009 at 11:44 am
Mani (1/6/2009)
thank you,So other than jobs created by maintenance plans, we can script out and and ran in sql server 2005 right?
yes. You may want to do a find\replace of servername before running it in, depends what the jobs do.
only other type of job it would not make sense to run in is those created to schedule the running of DTS packages.
---------------------------------------------------------------------
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply