Post reply

Changing collation of an existing server

  • September 14, 2004 at 11:23 am

    #102203

    Hi All,

    One of my servers has the wrong collation. I need to change it. Is there a way to perform that.

    I tried ALTER DATABASE on master but unsuccesfull.

    Any Ideas?

  • September 15, 2004 at 12:20 am

    #523103

    What you can try (no guarentee) is to change the collation of the model database. Since that is used as a template for new databases, in theory any new databases created on that server will have the new collation. You'll have to go and change the collation for existing dbs and the tables in them manually though.

    See the page in BoL "Changing Collations" under "SQL Server Architecture" for more info.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 15, 2004 at 1:00 am

    #523106

    BOL Rebuildm.exe

  • September 15, 2004 at 7:49 am

    #523166

    I have used the following procedure to successfully convert a server and all existing user databases from a Case-Sensitive to a Case-Insensitive collation. Merely running the rebuildm utility will not convert any existing databases to the new collation.

    1. Back up all databases for which you need to change the collation.

    2. Detach all databases for which you need to change the collation.

    3. Shut down all SQL services.

    4. Using the SQL Server rebuildm utility, rebuild the master database with the new collation and case-insensitive settings. See BOL. There may be a bug in rebuildm.exe which is addressed at http://support.microsoft.com/default.aspx?scid=kb;EN-US;273572.

    5. Reattach each database and export the data for each to a temporary database.

    6. Create scripts to recreate each of the tables and change the scripts' collation clauses to the case-insensitive version of the collation (for example, SQL_Latin1_General_CP1_CS_AS with CS toward the end is the Case-Sensitive collation, and you would change it to SQL_Latin1_General_CP1_CI_AS ....now with CI for Case-Insensitive).

    7. Drop each database and create a new empty version of each database.

    8. Run the scripts to create empty tables with the new collation in each of the new databases.

    9. Import the data back into each of the tables in each db from the temporary databases.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply