3

I am looking for an easy way to copy table data from a production SQL Server 2012 machine to a development machine (same version, level). We currently just do an export from within SSMS, but we have a couple hundred tables so the process is a bit arduous.

Have you seen any third party utilities that will do this?

I haven't tried SSIS for a few years, but it has historically been problematic and more time consuming to setup than it was worth. I don't need to do anything except copy the data into the dev tables (not an append, but a drop all the rows and insert the data). All the schema already matches.

Yes -- I have searched for the answer already... that's why I'm posting :)

Let me know and thanks.

---------UPDATE So how about this? Why not just link the servers; truncate the dev table and do a select into from prod to dev? I can script this out for the majority of the tables and not have to worry about a gui.

Downside?

Improve this question
6
  • 3
    Red Gate SQL Compare & SQL Data Compare
    – Aaron Bertrand
    Commented Jul 9, 2014 at 14:05
  • 1
    SSIS would still get my vote. You could always write your own script (with PowerShell, for example) but that's more work than necessary. That's a very simple task with SSIS.
    – Thomas Stringer
    Commented Jul 9, 2014 at 14:09
  • Idera SQL Comparison Toolkit - idera.com/productssolutions/sqlserver/sqlcomparisontoolset
    – Hannah Vernon ♦
    Commented Jul 9, 2014 at 14:10
  • I actually just downloaded SQL data compare as an option, but I really don't need to compare -- I just want to copy all the data so dev looks like prod from a data perspective. I ran the compare on three tables that had a couple hundred thousand records and it was a slowwwww process so I ruled it out. Thanks for the thoughts.
    – Dan Ribar
    Commented Jul 9, 2014 at 14:19
  • That's what data compare does - you can review the comparisons if you want, but just click the GO button and it makes the destination data look exactly like the source data.
    – Aaron Bertrand
    Commented Jul 9, 2014 at 14:20

3 Answers 3

Reset to default
3

Since you mentioned you want to copy table data only I guess SSIS would be perfect. I have been using it and it works just fine for me. It also depends on level of competency you hold with SSIS. You can go for backup restore but it would also restore tables whoes data you dont want to change. If you want complete refresh then of course no better option than backup restore.

You can go for import export wixard as well and its more easy than SSIS because it almost does all data conversion(if required) for you. You should use OLEDB provider always when data transfer is to be done within SQL server.There are tools but personally I found SSIS more relaiable than tools.

Have a look at below link

http://www.mssqltips.com/sqlservertip/2684/importing-sql-server-data-using-ssis--which-option-is-fastest/

Best practice for SSIS packages

Link

Improve this answer
2
  • I'll probably give this a shot. I haven't built a job for many years but remember they used to be quite time consuming and with close to 200 tables, it might be a bit of a pain to setup. Thanks for the data.
    – Dan Ribar
    Commented Jul 9, 2014 at 14:29
  • Gnereally, if you are inserting data into destination table and the detination table has lots of indexes operation would be slow.More I/O's required to update index as well. This could be one of the limiting factor. More over if you have to transfer 100 plus large tables you should use backup restore and after restore drop unused tables. ofcourse it depends on how much unused tables are present after restore. You need to strike a right balance. Quite a lot also depends on netwrok load on connection between prod and dev transferign data in offpeak hour would be better
    – Shanky
    Commented Jul 9, 2014 at 14:36
0

2 options :

  1. Preferred one : Backup with compression and restore it on Dev server. You can automate it using sql agent or PowerShell.
  2. Use BCP out and Bulk Insert in. I have tried and tested it if you want some tables only. Its very fast as I have implemented it in my environment as it suits my specific needs. You can even schedule it using sql agent job.
Improve this answer
3
  • Doesn't it seem like someone would have written a simple utility for this?
    – Dan Ribar
    Commented Jul 9, 2014 at 14:16
  • Not that I know of, but you should should try 1 and 2 that I mentioned. 2 is much faster than SSIS. Test it and you will know the difference. The link has a script written by me that will help you.
    – Kin Shah
    Commented Jul 9, 2014 at 14:32
  • A simple utility no. The Attunity Replicate product could be a simple to use solution. A few clicks to replicate, mostly source and target DB definitions. It can be configured to obfuscate some columns in some tables, and more. It's probably too much, too expensive for one-shot usage. Note: I work for Attunity.
    – Hein
    Commented Jul 10, 2014 at 17:36
0

We had to create a special tool to perform this function. Specifically, we needed to obfuscate sensitive fields, and since the database is so huge, if we are testing only part of the system, we could extract and load only the slice of the database (configuration data and transactions) that is relevant for the particular software being tested. The underlying mechanism is Bulk Insert BCP out/in. If it wasnt for those requirements, I would do a backup/restore to test environment (which would be on a different LAN)

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.