1

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

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.
| improve this answer | |
  • @Mizokunimito Be sure to read all of the potential issues with the -q approach. I'm guessing that it's not officially supported due to the very nature of collation changes being inherently messy. The main issue is likely the lack of code page conversions, even more so if going from a single-byte to double-byte character set (an edge case). There are, I think, a few other potential pitfalls that I have tested but haven't had time to include in my post, dealing with changing case-sensitivity as it relates to existing object names that are used in check constraints and computed columns. – Solomon Rutzky May 13 at 15:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.