Search code examples
c#sql-serverssislinked-serverbcp

how to get test data from production to dev with least performance impact on prod?

Frequently we need to get some test data from our production sql server 2008 (non r2 enterprise) to our development sql server 2008 r2. Obviously when we do this data extract we want to have minimum performance impact on the production box.

Which is the best method ?

1) bcp out , bcp in
2) ssis package etl
3) linked server insert into dev.table select from prod.table
4) C# app

There maybe other ways, but I have only used above 4. Please advise. thank you

Solution

  • If you are interested in just taking the backup without modifying any data then BCP is the way to go .It's very fast option that requires minimal parsing of the text file input.You can create a batch file where in you write your BCP OUT and IN commands and execute it whenever you need to copy data .

    For more information on different loading strategies please refer this article