0

I tried to find how do I restore the database to a different collation.

For example:

  • Server collation is Czech_CI_AS
  • Database, which I backed up, has a collation Czech_CI_AS.
  • And I need to restore a backup of the database, but with the collation SQL_Latin1_General_CP437_CI_AI.

Somewhere I'm doing something wrong, will you help me?

Dan Beaulieu
  • 19,406
  • 19
  • 101
  • 135
HoP
  • 3
  • 1
  • 1
  • 4

1 Answers1

2

As you've discovered, the collation of the original database is preserved in the backup, and so when you create a new database from this backup somewhere else, the original collation comes with it. There is no option on the restore command to change collation during the restore.

You could ALTER DATABASE to a different collation after you have restored the database, but note that this will not change the collation of all the existing objects in the database, you will need to alter the collation of columns in individual tables yourself.

See MSDN:

When you change the database collation, you change the following:

  • Any char, varchar, text, nchar, nvarchar, or ntext columns in system tables are changed to the new collation.

  • All existing char, varchar, text, nchar, nvarchar, or ntext parameters and scalar return values for stored procedures and user-defined functions are changed to the new collation.

  • The char, varchar, text, nchar, nvarchar, or ntext system data types, and all user-defined data types based on these system data types, are changed to the new default collation.

You can change the collation of any new objects that are created in a user database by using the COLLATE clause of the ALTER DATABASE statement. This statement does not change the collation of the columns in any existing user-defined tables. These can be changed by using the COLLATE clause of ALTER TABLE.

Bridge
  • 29,818
  • 9
  • 60
  • 82
  • Collation is stored at instance, database and column level, not table. You have to alter each of the text-based columns individually - see [this related SO question](http://stackoverflow.com/q/16730114/1220971) for a script to generate the alter statements for you. – Bridge Sep 16 '15 at 14:04
  • Thanks for your time! – HoP Sep 16 '15 at 14:11