2

I'm not sure if this is the best way to migrate data to another collation but I've done the following:

  1. Scripted the source database and changed the collation in the script to my required one
  2. Ran the script and created the new database with the wanted collation (along with all the tables,triggers,indexes etc.
  3. I tried to 'Export Data' from the SQL Server management studio but I got an error related to code page differences.
  4. Since Export/Import Data didn't work, I resorted to using bcp to mass export all the tables in the source database to txt files. After that I began importing the rows using bcp but I saw some errors related to truncation and invalid characters.

What's the best/proper way to migrate the data from a database with a different code page & collation after creating the clone from the scripted source?

Improve this question
2
  • What collations would the source and destination be? You are likely to need explicit data conversion rules.
    – vonPryz
    Commented Feb 29, 2016 at 12:34
  • @vonPryz The source collation is SQL_Latin1_General_CP1_CI_AS (code page 1251) and the destination collation is Arabic_CI_AS (code page 1256). How do I go about converting?
    – AuthenticReplica
    Commented Feb 29, 2016 at 12:39

1 Answer 1

Reset to default
2

You have several options:

  1. In SSIS, in the SSIS ToolBox there is a component called Transfer Database task.
  2. use the method provided here
  3. use the method provided here

I would probable go with 3), but that is a matter of taste.

Improve this answer
4

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.