February 13, 2014 at 2:52 am
Hi Experts,
How can i move jobs from SQL server 2008 R2 to 2012. We have around 50,000 jobs in source server.
I tried 'TRANSFER JOBS TASK' in SSIS but it failed in between because of a database reference which is not there in new server.
How about restoring MSDB?
February 14, 2014 at 6:48 am
I wouldn't restore MSDB froM SQL2008 to 2012.... I am sure there are table changes between the two different versions of SQL Server....
Man that is a LOT of jobs.... I don't know what to tell you... hopefully someone will chime in on how to do that.
February 14, 2014 at 7:04 am
Wow!!! 50,000! I script jobs frequently, but my quantity is usually much, much less. I suggest that you check to make sure the database mail profiles and SQL Agent operators are identical on the 2008 and 2012 servers. Then, attempt scripting the jobs using the GUI. Are they broken down into categories? Perhaps work with one category at a time.
February 14, 2014 at 7:17 am
What i did is ,used SSIS transfer job task and for some failed jobs ,i scripted out those and executed .
February 16, 2014 at 9:46 pm
50K jobs Great!!! Nice to see the capability of SQL Server to handle this much jobs with ease 🙂
Definitely restoring MSDB in different version is not a good idea. You are on the right track by transering jobs using SSIS. What you can do is list the jobs which fails (you are not able to fix in SSIS) using SSIS and script those jobs and execute the script manually. Also exclude the failed jobs from SSIS package.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
February 19, 2014 at 6:22 am
I agree you all. But am imagining like restore a backup of msdb to another instance of same version and then upgrade to newer version, then restore to target msdb.??? Beleive it will take lesser time to transfer 50K jobs manually.
February 19, 2014 at 7:29 am
If you're going to do it right the first time you need to see if the jobs are all active, are they needed, are they split into categories. The jobs that failed are most likely the product of some small thing being off or possibly missing in 2012, I don't have the packages in front of me so I'm purely speculating at this point, but possibly some things to think about. Also check if the jobs that failed have some reference (i.e. script task using c# that needs a library to run). Hopefully you'll be able to better source the problem, but you should take your time figuring out the issues so that everything runs as it should , better to be safe than sorry 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply