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.

  • What do you mean "in the same way" what is that actual problem?
    – Nick.Mc
    May 21, 2017 at 22:40

1 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.

  • 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. Jun 3, 2016 at 8:42
  • 2
    @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.
    – user_0
    Jun 3, 2016 at 8:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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