Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm moving from SQL Server 2005 to SQL Server 2008R2, and I see there is one job in the SQL Server Agent Jobs that I need to move but I see it also in the SSIS---> Stored Packages\MSDB\File Manipulation. Not sure if I right click on it and choose export, would that affect the production server if it wont be working in the new dev server? or how I can import just a copy of it to the other new development SQL server 2008?

Thanks for any advice or direction.

share|improve this question
hope this helps[… – Biju jose Dec 19 '13 at 5:28
Did you re-created job with schedule ? If yes try to remove it and re-create only job. Once done test it. Once happy add new schedule. – Kuba Miazek Dec 23 '13 at 12:44
I'm afraid to change anything for now as tomorrow is the day to take this server offline and move to the new one. But I will practice it later this week with a dev database and create a job for that as I'm still not clear how different the steps vs schedules and plans are. – Shayma Ahmad Dec 23 '13 at 20:18
up vote 2 down vote accepted

Well you must be carefull when scripting out jobs. Especially jobs related to replciation.

Plus there is also sp_add_jobschedule and you need to decide that you want it in same shape on both servers. If yes you can re-create it but don;t use same @schedule_uid=N''

share|improve this answer
thanks for your advice, yes I need them to be the same in both servers exactly. How can I change or give it a new @schedule_uid? as I'm using the scripted out job from the old server and changed the connection to the new server, much appreciated. – Shayma Ahmad Dec 19 '13 at 16:50
Just remove it from creation script and new sid will be added automatically – Kuba Miazek Dec 23 '13 at 12:42

If you're talking about moving SQL Server Agent Job, I suggest you use the option "Script Job as -> Create" and than copy and paste the code on the new server. Once you have the code on the new server you can edit it (or execute it and than edit the job thorough Job Properties window) so it could work in a new enviroment.

share|improve this answer
Thank you for your answer. I did script it out and changed the connection server and the file path for the SSIS file but when I excute, it gives me the following error: – Shayma Ahmad Dec 19 '13 at 16:39
Msg 8114, Level 16, State 5, Procedure sp_add_jobschedule, Line 0 Error converting data type nvarchar to uniqueidentifier. ----there is no line zero though? is it because I have omitted the @schedule_uid = N'' or I need to re-create the SSIS package first in the Integration Servics? and not in SSMS? as I see the following in the scripted out code: – Shayma Ahmad Dec 19 '13 at 16:46
@os_run_priority=0, @subsystem=N'SSIS', @command=N'/FILE "C:\SSIS\Packages\elfcopy.dtsx" /CONNECTION "BRORPORAP3.WDDocumentManagement";"Data Source=BRORPORAP3;Initial Catalog=WDDocumentManagement;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;" /CONNECTION RCSMTP;";UseWindowsAuthentication=True;‌​EnableSsl=False;" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E', @database_name=N'master', – Shayma Ahmad Dec 19 '13 at 16:47

In SSMS, go to SQL Server Agent Click on Jobs

Now Click on View->Object Explorer Details

You will have a list of all jobs on your right window. This way you can select multiple jobs that you want to script, right click and select "Script Job As" and then the options that you want.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.