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


Scripted the source database and changed the collation in the script
to my required one
Ran the script and created the new database with the wanted
collation (along with all the tables,triggers,indexes etc.
I tried to 'Export Data' from the SQL Server management studio but I
got an error related to code page differences.
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 &amp; collation after creating the clone from the scripted source?
You have several options:


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


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