• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

Copying data over the WAN

I need to get a table with data from accross the wan at our company.  Actually the data is in Chicago and I am in California.  The table I need to get over to my local sqlserver database has 12million rows.  It took 4 hours to get 3million, so 12 hours to get the data is way too long.  Does anyone know of a better solution?
0
donnie91910
Asked:
donnie91910
  • 3
  • 3
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
create a new database in the source server and using dts or BCP copy the contents of that table to that db.
truncate the log and take the backup of that db, copy the backup thru remote desktop or FTP
restore it over your server, use dts to move the contents to ur machine
0
 
donnie91910Author Commented:
Which is faster dts or BCP copy.  I used dts and it was going to take 12 houurs.
Thank you.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
BCP is faster,
the approach what you did was copying data from one server to the other thru network, this will be slow. Meanwhile if you follow the approach i mentioned above, then dts will yield almost the same performance
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
donnie91910Author Commented:
2x faster or less.  The reason why I ask is because the report that I am working on is time sensitive and needs to be out by 2pm each day.
Do you BCP to a file and then import the data into the SQL Server database?  
What is the syntax for BCP.  
Sorry for the questions, kinda new to BCP.
Thanks
0
 
donnie91910Author Commented:
I only have one table but it is 12million rows.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
if you just have only one table, then the BEST approach is to take the backup of the Database first, use winrar to compress that backup (if you are not using  any third party s/ws like litespeed or red-gate backups , for backing up ). copy those compressed files over the network, and restore it over the client side.  this will be more than 10 times faster than your approach.

for BCP, check books online
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now