What is a good way to transfer a table from one SQL Server instance to another server with an available FTP?
-
2FTP? Read up on linked servers.Oded– Oded2011-08-29 17:12:46 +00:00Commented Aug 29, 2011 at 17:12
-
If both servers were on our network… I could see this being a possible workaround. With the servers being on two disconnected, intentionally segregated networks… it changes the landscape of what we are trying to accomplish. FTP isn’t the only option, it is the desired option. To connect the server running our internal SQL servers with this PC that is externally available to the whole world would be… ill advised… to say the least.Troy Mitchel– Troy Mitchel2011-08-29 17:49:41 +00:00Commented Aug 29, 2011 at 17:49
-
1Check out Red Gate SQL Compare and SQL Data Compare.Yuck– Yuck2011-08-29 17:51:28 +00:00Commented Aug 29, 2011 at 17:51
Add a comment
|
3 Answers
Use 'Generate Scripts' to script table creation and use the SSMS Tools' 'Generate Insert Statement' feature to create a script to populate the table. Simple, ad completely disconnected.
And alternative would be to backup the DB, restore it to a parallel DB on the new server, and copy from DB to DB (via SELECT INTO)
1 Comment
Troy Mitchel
thanx. we findally got around to getting the SSMS tool and it worked. It created the file, then i doubled clicked the file on the other server and executed it. The only preliminary step that needs to be done before executing the script is to [Delete * From TableName] otherwise it wont insert the new rows.
Have you tried using a linked server from server A to server B?
Comments
Here's one way (using TASKS via SSMS): http://msdn.microsoft.com/en-us/library/ms142159.aspx
Here's another way (using SSIS): http://msdn.microsoft.com/en-us/sqlserver/cc511477.aspx
1 Comment
Troy Mitchel
I would like to use the TASKS however the DB manager frowns on the direct server link between the 2 servers hence (security issues) because one of the servers is for outside customers to use. So we only have an FTP available.