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 & collation after creating the clone from the scripted source?