DTS

Question

DTS

Jay Murthy

Say Hey Kid

Points: 673
More actions
June 1, 2006 at 10:39 am

#169724
Hi,
Is it possible to create a DTS package which will take whole Database and move it to another server.
My requirements are
- Source Database can change from time to time and I want DTS to take the changes when it runs.
- I want to run it through Command prompt and supply Source/Destination Server/Database.
I will really appreciate any Help.
Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

David McFarland SSChampion Points: 11815 More actions · Answer 1

We usually make large moves like that by detaching, copying, and reattaching, but you could probably get it to work with the Copy SQL Server Objects task, copying everything. The only thing it probably won't create is SQL Logins, but as long as they already exist on the destination server, I'm pretty sure it will even transfer ownership and permissions.

It won't be fast, which is why we use the detach/attach method, but it should work.

As an alternative, you could try an automated solution using sp_detach_db, your preferred copy method, and then sp_attach_db (or sp_attach_single_file_db if you don't have multiple files per db), but keeping in mind that sp_attach_db requires you to know the name of the physical file(s), which sp_detach_db doesn't give you, so you'd have to go dig into system tables to get it. If you do go this route, I'd test the everliving hell out of that with test databases before I'd put it into production.

As for the command prompt, you can wrap a DTS call in a batch file by calling dtsrun, and you could pass the batch file parameters through to the dtsrun parameters.

Jay Murthy Say Hey Kid Points: 673 More actions · Answer 2

Thanks for you reply. But, my requirement is little different. We are writing a application which will be given to our tech guys and they will specify the Source DB/Server and we will need to move DB. Therefore I wanted to do it through DTS.

I did not see any parameters for Destination Server/DB in help for DTSRUN.

Anyone?

Thanks.

David McFarland SSChampion Points: 11815 More actions · Answer 3

It still sounds like your requirements are what I posted about above. Both of the automated methods I outlined are DTS based, and you can pass global_variables to DTSRUN, which are used as parameters within DTS.

If you're not attempting to automate the moving of a database from one server to another, with parameterized database and server names, then I'm missing something. Otherwise, my post above discusses doing exactly that.

The most straightforward method would be the first one, the Copy SQL Server Objects task. The second one would just likely be faster, but would take substantially more upfront work and testing.