0

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

Gullu
  • 3,477
  • 7
  • 43
  • 70

2 Answers2

1

If your SQL server has nightly backups, you could copy the backup over to the DEV server and restore dev with the backup. That would impact the production SQL server the least.

Losbear
  • 3,255
  • 1
  • 32
  • 28
  • whole bakup is too big. We just need to carve out some data from specific tables. thx – Gullu Jul 11 '12 at 15:11
  • then you'd need to restore the backup to a temporary location and then perform the extract of the data you're after. – joocer Jul 11 '12 at 15:14
  • My question is really best way to do. (not best time to do it - like nightly backup). Again it is not to do with backup. It is for getting test data for developers. Many times depending on which module the developer is working on we need to carve out data for specific tables. No need for whole db. – Gullu Jul 11 '12 at 15:24
0

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

praveen
  • 12,083
  • 1
  • 41
  • 49
  • thx for link. Big article. I scrolled direct to conclusion and it looks like ssis is the way to go. – Gullu Jul 11 '12 at 15:25
  • SSIS will surely help u to transfer data at a much faster rate since there is no intermediate text file which are used by bcp commands but bcp are really helpful in scenarios where you don't need any data manipulation and more over the query can easily be written – praveen Jul 11 '12 at 15:30