February 26, 2008 at 12:02 pm
We need to copy all jobs and DTSs’ from one server to another server.....what is the best way to do it ?
Also does the DTS packages/settings have connection to production ?
how can copy all transfer all jobs but replication ?
February 26, 2008 at 12:51 pm
cqldba (2/26/2008)
We need to copy all jobs and DTSs’ from one server to another server.....what is the best way to do it ?Also does the DTS packages/settings have connection to production ?
how can copy all transfer all jobs but replication ?
Best way to transfer DTS packages to other server is to save DTS package into Structured storage file and open it on the server you want to and save it.
You might have to change the connections where ever necessary.
Moving msdb tables to other server would be one option, but I prefere to generate scripts of job by right clicking and execute it on the other server and then make necessary changes.
SQL DBA.
February 26, 2008 at 6:26 pm
is the source server in sql server 2000 or sql server 2005?
if it is sql server 2000, you can do it by creating a dts package:-
1) In Enterprise Manager console, expand server -> Data Transformation services
2) right click on Local Packages and select New Packages. DTS Packages window will be opened
3) under the Task, drag the icon "Transfer Jobs Task" into design panel (left white area).A window called "Transfer Msdb Jobs Properties" wil be popped up
4) in the source tab, specify the name of source server. in destination tab, specify the name of destination server. in the job tab, select any one of the option. if you want all jobs to be transfered, click on "select All"..
5) Click on OK, the jobswilll be transfered to destination server.
Thanks
Joseph
February 28, 2008 at 5:30 pm
You have the transfer jobs abd DTS tasks in the DTS so yo can use them to move to the other server.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 28, 2008 at 10:04 pm
I am using a tool from internet called DTSBackup. It is a handy tool especially if you are transferring numerous DTS packages...this tool can transfer from one server to another directly. you can also use it to save it to .dts file then transfer to another server. use it again to upload the package from .dts to SQL server.
Well, of course.. if you are not using dynamic properties.. you have to modify the connections et al manually...
for transfer of jobs.. you can generate the scripts for all your job then have it run on the master of the destination server.. again.. modify the connections et al first.. either in the script or when you executed ithem in your destination server...
:-):cool:
February 29, 2008 at 8:54 am
For a freebie, look into
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply