Changing the Server Collation

Changing the default collation for an instance of SQL Server can be a complex operation and involves the following steps:

  • Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.
  • Export all your data using a tool such as the bcp Utility. For more information, see Importing and Exporting Bulk Data.
  • Drop all the user databases.
  • Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setupcommand.For example:
    Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName
    /SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ]
    /SQLCOLLATION=CollationName

     

    For more information, see Rebuilding System Databases.

  • Create all the databases and all the objects in them.
  • Import all your data.