October 15, 2013 at 7:40 am
Hi Friends,
I have around 100 tables to copy from one server to another. The problem is that these 2 servers are not in the same network. Is there any way in which I can achieve this. May be copy data in a disc and and then copy it to the other servers.Please could you suggest few ways of doing this.
Thanks
October 15, 2013 at 8:34 am
mishrakanchan86 (10/15/2013)
Hi Friends,I have around 100 tables to copy from one server to another. The problem is that these 2 servers are not in the same network. Is there any way in which I can achieve this. May be copy data in a disc and and then copy it to the other servers.Please could you suggest few ways of doing this.
Thanks
How much data are we talking about? If it were me, I would probably use BCP to export the data from the 100 tables into individual files, then transfer them to the second server using an FTP site (or SFTP site) both servers can access, cloud storage, USB drive or some other technique Once transferred, you can use BCP again to load the data into the tables on the second server. Sensitivity of the data should be a concern as well. If the data is sensitive, then I would want to use a secured method to transfer the data to the second server.
October 15, 2013 at 10:28 pm
I am thinking of doing this. Please suggest if this will work or not..
the 100 table make about 100 GB. This is what I am planning to do.
Step 1. Copy the 100 tables to a new database in the local server
Step 2 -I take full back up of this database (with only those 100 tables)
Step 3 - I somehow place the .bck and .trn files (I mean data and log files) generated from step 2 in the new network . I can do this through USB or any other mechanism
Step 4 - I restore these files in another database in the new network
Please suggest if this will work
October 16, 2013 at 1:39 am
If the database servers have SQL Server authentication and TCP/IP enabled, you could use SSIS to transfer the data.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 16, 2013 at 8:18 am
mishrakanchan86 (10/15/2013)
I am thinking of doing this. Please suggest if this will work or not..the 100 table make about 100 GB. This is what I am planning to do.
Step 1. Copy the 100 tables to a new database in the local server
Step 2 -I take full back up of this database (with only those 100 tables)
Step 3 - I somehow place the .bck and .trn files (I mean data and log files) generated from step 2 in the new network . I can do this through USB or any other mechanism
Step 4 - I restore these files in another database in the new network
Please suggest if this will work
A backup and restore should work as long as the versions of SQL Server allow it. I can't tell from your post though if you're planning to do a RESTORE DATABASE or detach the database and copy the data files to the second server, then re-attach them. If you take a full backup of the database, you should be able to copy the .BAK file (or whatever you name it) to the second server on a different network. You'll then want to do a RESTORE DATABASE and will likely need to use the WITH MOVE option to place the database data and transaction log files in an appropriate directory on the second server.
As Koen suggests, you could potentially use SSIS but that would require network connectivity between the two servers over the appropriate TCP/IP port.
Are the two servers running the same version of SQL Server?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply