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?

Dan Ribar
  • 51
  • 4

3 Answers3

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

Glorfindel
  • 2,205
  • 5
  • 19
  • 26
Shanky
  • 19,148
  • 4
  • 37
  • 58
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.
Community
  • 1
Kin Shah
  • 62,545
  • 6
  • 124
  • 245
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)

Mike A
  • 11
  • 1