As a part of an upgrade I had to change the collation of a database. “How hard can that be?” I thought in my naivety.
Writing an sql script with an alter database and a collate command should do the trick if it wasn’t for a long list of dependencies showing up like this:
Msg 5075, Level 16, State 1, Line 2
The object ‘CK__KMVIRTUAL__RECID__00026DDC’ is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
The solution I used to move forward was to use the script building function in the SQL server. It allowed me to by a wizard get a script to create a complete database with tables, logins and all.
When I had that in the query window I could search/replace the original database name with a new one and add this piece of TSQL in the database creating part:
ALTER DATABASE <MyNewDatabaseName>
After running the complete creation script I had an empty database with the right collation.
Next step was getting data into the database. Using the Import wizard I could pull in the data from the original database into the new one and since the tables were all called the same I did not have to do any actions besides selecting the tables in the table list in the wizard.
Running the import job took some time but nothing compared to the alternatives.