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
Best Answer
You should first attempt executing:
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 thecollation_id
of each relevant column (relevant = column that can be changed; some system columns cannot be changed). This can lead to data corruption inVARCHAR
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:
sqlservr.exe -q
method to update this temporary instance to the instance being used by the target instance.