Setting and Changing the Server Collation

The server collation acts as the default collation for all system databases that are installed with the instance of SQL Server, and also any newly created user databases. The server collation is specified during SQL Server installation. For more information, see Collation Settings in Setup.

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 setup command. 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.

Note

Instead of changing the default collation of an instance of SQL Server, you can specify a default collation for each new database you create.

See Also

Reference

Collation and Unicode Support

Concepts

Setting and Changing the Database Collation

Setting and Changing the Column Collation

Setting Expression Collation

Identifier Collation

Rebuilding System Databases

Change History

Updated content
Corrected the syntax required to rebuild the master database.