April 5, 2005 at 8:55 am
We currently have a PPTP connection set up for our developers to access our development SQL server through a VPN tunnel. When they need to copy tables up to our dev SQL server from their local SQL server they simply do a DTS copy.
However, we are now moving to a thin client solution where they will be working on a terminal server. They will have access to the development SQL servers and SQL tools such as EM and QA. However, they will not have access to their local SQL server due to the firewall in between them and the terminal server and, therefore, will not be able to directly perform DTS copies. We have explored several possibilities such as exporting tables to a .csv or .mdb file and then importing them on the development SQL server but this is not ideal because things are lost in that process (e.g. primary keys, field names, data types, etc.). It would be possible to just script a table in EM and then use bcp to insert the data on the target server but I was hoping for a simpler approach with fewer steps. Some of the developers are not so SQL saavy and we would end up supporting them to do these SQL object copies if the process requires too many steps.
My question is this: Is there a way to export and then import SQL tables without losing dependent objects such as primary keys and data types in the process? If any of you are working with a similar situation I would really like to hear how your remote users copy objects from their remote location to your SQL servers when there is a firewall in between blocking traffice on port 1433. Thanks!
Ryan
April 5, 2005 at 10:09 am
One option to look at is detaching the database from the local SQL server then copying the database files to the development SQL server. If you are going to update the whole database, you can simply detached the database from the development SQL server then use the new database files. If you just want certain tables and not the whole database, attach the transferred database files as a new database with a different name and you can initiate the transfer on the development SQL server using DTS copy.
Hope this helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply