Sql-server – Cloning a database to another collation

collationmigrationsql server

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?

Best Answer

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.

Related Question