Migrate several jobs to another server

Question

Migrate several jobs to another server

lulu_lutz

SSCrazy

Points: 2155
More actions
September 21, 2011 at 8:58 am

#243433

I have a sql server that has around 20,000 jobs that need to be migrated to another server. Both boxes are sql server 2005 SE. Obviously scripting the jobs is out of the question. Any ideas?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

sqluser_8119 (9/21/2011)
I have a sql server that has around 20,000 jobs that need to be migrated to another server. Both boxes are sql server 2005 SE. Obviously scripting the jobs is out of the question. Any ideas?

Because??

Scripting 100 jobs here takes only a couple seconds.

Anyway you can just backup/restore msdb from the old to the new server?

lulu_lutz SSCrazy Points: 2155 More actions · Answer 2

I tried scripting and it made the server freeze. Are there any implications with restoring an msdb to another server?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

sqluser_8119 (9/21/2011)
I tried scripting and it made the server freeze. Are there any implications with restoring an msdb to another server?

You froze the server or your local machine (please tell me they are not the same)?

I can't really see this killing the server unless you have only 1 processor.

I can't confirm the full implications of restoring msdb as I've never done it myself. But obviously you'd lose everything on the new server (jobs, ssis, permissions, backup & job history).

I'm sure there may be others, hence I can't comment any further.

lulu_lutz SSCrazy Points: 2155 More actions · Answer 4

Why would you even suggest that I am so unexperienced that I would have my machine acting as the server? That was extremely rude. Forget I even posted the question.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

sqluser_8119 (9/21/2011)
Why would you even suggest that I am so unexperienced that I would have my machine acting as the server? That was extremely rude. Forget I even posted the question.

I didn't mean any offense. We just get lots of total newbies and this error could have been very costly.

Any reasons you can't just start the scripting job overnight and let it run?

Otherwise you'd have to learn the msdb upgrade on a test server (and maybe script from there if it can run 24 / 7 as needed).

Overwriting the real msdb would be my absolute last resort.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

P.S. I was actually referring to RDPing to the prod server instead of connecting from local SSMS.

BoobyB SSCertifiable Points: 5470 More actions · Answer 7

Hi,

another option would be to use SSIS package (transfer job task) and use the option of overwrite at the destination.

u can let this job run in the night or so when server is not busy.

please see the article below.

http://msdn.microsoft.com/en-us/library/ms137568%28SQL.90%29.aspx

ps - the first option mentioned by another user works just fine for me too.

Lowell SSC Guru Points: 323518 More actions · Answer 8

i think you have to script the jobs out; as i remember it, the msdb rows refer to the server name, so if the "new" server is not named the smae, the jobs from a msdb restored from a different server will fail.

if you have the list of job names, there are plenty of script contributions here on SSC to help scripting them out manually, isntead of via the GUI.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 9

Here's one using SMO, which is what I think the GUI uses, though perhaps less efficiently.

http://www.sqlservercentral.com/scripts/Maintenance+and+Management/30483/

It might appear to freeze the client, as SSMS might do, but if you have 20,000 jobs, it will take some time. The jobs are just entries in tables. I'd assume you could bcp out/in the data (or SSIS) it from server to server, but I'm not sure I'd do that. An msdb restore will work, but I wouldn't do this unless it's a new instance you are migrating to and there is no history/jobs/packages in there.

20,000 jobs seems like a lot for the server to handle, you'd have to have regular collisions in them running. Are these all active? Just curious on why you have so many.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 10

Scripting would be the only way I'd consider it. Even if you just backup & restore msdb to the other server (which won't work), you still need to deal with the fact that you have 20000 jobs that might refer to ServerA when you're on ServerB. I'd use scripting because it's going to allow you to update and modify code prior to applying it to the new server.

Also, Ninja is just trying to help. He's not in your shop so can't see what you can see and all he has to go on is what you typed. You'd be surprised at the insane things people will do with their servers and we see it go by on here all the time. That's where his comment came from. He's a solid guy and had no ill intent.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning