August 25, 2006 at 2:02 pm
I have over 50 jobs on a server that we need moved to another server. I don't want to have to spend a couple of hours recreating each job, is there a way to backup and restore (or copy) jobs from one sql server to another?
Thanks,
Adam
August 28, 2006 at 2:43 am
The only way I found to do it was to generate the scripts and then run each on the other server.
If there's an easier way I'd love to know too!
Flic.
August 29, 2006 at 8:32 am
Since job information is stored in msdb, you could backup and restore the database. This is only an option if you're moving to a new server that doesn't already have jobs on it.
I usually go the scripting route that Felicity mentioned. There are a couple of scripts in the script section of this site that claim to script out all the jobs on a server.
Greg
Greg
August 29, 2006 at 1:06 pm
MSDB restore can be dangerous as it will overwrite the jobs on destination server.Use Enterprise manager right click on jobs and Choose "Generate SQL Script".
August 29, 2006 at 2:50 pm
Well the destination server has nothing of importance on it, so would backing up and restoring the msdb be ok to do?
August 30, 2006 at 9:23 am
Yes. Also make sure that SQL Server Agent is started on the destination server.
Greg
Greg
September 4, 2006 at 3:13 am
You can transfer all the jobs using DTS. Go to enterprise manager => select server => Data Transformation Service => local packages
Make new package here by selecting "Transfer Jobs" from the 'Tasks' menu. Give credentialsfor the local and the target server. Execute the package. Thats it.
All the best
Regards
Pankaj
Pankaj Khanna
Database Administrator - SQL Server 2000
Keep hope to keep you intact...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply