Copying Partitioned table data to another table on different SQL Server

Question

We have a partition table on Production environment with 2338117620 rows and 206 partitions. Now we have to copy only last 2 year data in Test environment.

But the problem is that , Import Export utility does not copy partition tables the same way in target database even if we have created the same partition reference on the target database.

So, now we have only one option left is that of Bulk-copy. But not sure if bulk copy supports exporting partition data to a target partitioned table. Do let me know your thoughts on it.

• Nick.Mc: What do you mean "in the same way" what is that actual problem?

Answer 1

BCP works fine.

• First, you create the target table.
• Then export data with the select and filter you need.
• Import data with BCP will fit the correct partitions.

I did this before. I was on SQL 2014, but it will be fine on 2012.

• Karan Salvi: Yes that worked for me. But while copying the data, the txt file size is about 42GB which is giving me error. SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Server Native Client 11.0]Unable to open BCP host data-file Is there any size limitation while using IN for copying the data into target table.
• user_0: @KaranSalvi I dont know about a limitation but I strongly suggest: use native format; check permissions on filesystem, do commit every few thousands of rows and if possible, export your data in multiple smaller files. In my case I used to work on few months of data per file.