Copying or Exporting Jobs 1

[Jenns]

How do I copy or export SQL Jobs and DTS packages from one server to another.

We got another server to use for testing and development. I need to have everything on the old server copied to the new. I copied the tables, views and stored procs but need the jobs and DTS packages.

I'm running SQL7 on NT.

Thanks,
Jenn Thanks!
Jenn

 

[Guest_imported]

Ok Jen to copy the DTS Packages.
Open the package.
Then on the tile bar click on Package.
Go down to save as.
A window will pop up and there is a drop down box in here that says server. It probably reads "local" or the name of the server you are working on.
In this drop down menu you should have your other servers in this list also. Choose the server name you want to move this DTS to and click ok. Note you will still have a copy of the DTS on the original server.

Matt C.
ciavle_m@subway.com

 

[Guest_imported]

oops

civale_m@subway.com

 

[Jenns]

Matt,
It will take some time but it is working. I was hoping to find something in a batch mode, but this will do.

Are there any suggestions on how to copy the jobs? Thanks!
Jenn

 

[Guest_imported]

Jen

For the Jobs
Open Management Folder and expand SQL Server Agent.
Click on Jobs so they are all displayed in the window on the right.
Click on the first one.
Then hold down Ctrl click all the ones you want.
Right click on these go down to ALL TASKS and over to SCRIPT JOB.
Give it a file name this will save it Under MSSQL7 then BINN folder by default. (I would save it to the desk top) Click ok. Open the Query Analyzer and choose the other server click ok. And open this file you created in there and run it.
I'm trying to see if there is an easier way for the DTS.
Matt C.

 

[Guest_imported]

Open DTS and Local Packages.
Click on Local Packages so they are all displayed in the window on the right.
Click on the first one.
Then hold down Ctrl click all the ones you want.
Right click on these go down to ALL TASKS and over to Export Data.
Click NEXT
Choose Data Source
Everything is ok here by default!
Click Next
Choose Destination
Click the drop down Box and choose the other server leave the database as default.
Click Next
And this should be all that you have to do. Proceed to the finsh and let it run.
Matt C.

 

[jhall156]

right click on jobs and then select all tasks|Generate SQL Script, preview it, press copy (to the clipboard).
open your other sql server, tools|query analyzer
paste the script and f5.
This will copy all the jobs. If you only want one then instead of right clicking on the jobs node, right click on the job name in the right hand pane and then follow the rest.
JHall

 

[Jenns]

Thank you both. You have been extremely helpful!

Thanks!
Jenn

 

[Guest_imported]

How do I create step by step procedures of what a DTS package does? Is it possible?

Neal