Change collation in easy way

Question

I'm a developer and sometime I face with restoring a DB with a different collation than the server. Our application uses the tempdb too and as result some joins fail due of the different collation. So my question is : does exist a tool or a standard approach to convert the DB without doing the entire process manually? Often the collation is very similar changes by a different default on a newer edition of Sql Server such as SQL_Latin1_General_CP1_CI_AS vs Latin1_General_CP1_CI_AS, in these cases the process of export/import data should not be necessary.

Thanks

Answer 1

You should first attempt executing:

ALTER DATABASE [{db_name}] COLLATE {new_collation_name};

If you get errors, are they something simple to remove, then change the collation, then re-create, such as a small number of functions? If so, then do that.

If the errors related to items that cannot be easily changed, or are far too many things to change, then the next easiest way is the undocumented sqlservr.exe -q method. That does a simple meta-data change of the collation_id of each relevant column (relevant = column that can be changed; some system columns cannot be changed). This can lead to data corruption in VARCHAR data if the code page changes, but in your case the code page is not changing. I have documented what this command does here:

Changing the Collation of the SQL Server Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

If the instance has several databases on it that will not be changing, and/or if the instance-level collation itself will not be changing, then you can:

1. Create a temporary instance of the same version of SQL Server (be sure to specify a collation that is not the collation of the target instance where this database will ultimately be restored to).
2. Restore this one database to that temporary instance
3. Use the sqlservr.exe -q method to update this temporary instance to the instance being used by the target instance.
4. Backup / Detach this database from the temporary instance.
5. Restore / Attach this database into the target instance.
6. Assuming everything works as desired, stop and uninstall the temporary instance.