question

Kiran 1 avatar image
Kiran 1 asked

generate script for all jobs of sql server and recreate them on another instance

Hi,

Please let me know how to generate script for all SQL Server Agent jobs and recreate them on another instance.

Thanks, Kiran

sql-agentscriptjob
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

CirqueDeSQLeil avatar image
CirqueDeSQLeil answered

You can use a tool such as Redgate SQL Compare and Redgate SQL Data compare.

You can script each job one by one - right click the job, script job as, create to, new query editor window.

Or, you can backup and restore your msdb to the other database server.

You can also use an SSIS job to transfer the sysjobs tables. You should probably also transfer the backup* tables as well as sysdts - in case you have ssis or dts packages saved in msdb.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

If you are setting up mirrorng, you should not need to script the database to get the copy the database. As per the instructions on setting up mirring, these might come in handy, you simply have to do a restore of a recent full backup.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kiran 1 avatar image
Kiran 1 answered

Thanks for replying. I do not want to script the database. I want to script out all the agent jobs on one instance and cpoy them to another instance. This is the requirement.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.