Setting and Changing the Server Collation

Setting and Changing the Server Col...

Community Content

In this section

Statistics Annotations (1)

Click here to expand

Collapse All

Language Filter

SQL Server 2005 Books Online (November 2008)

Setting and Changing the Server Collation

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

Changing the Server Collation

Changing the default collation for an instance of SQL Server 2005 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 bulk copy.
Drop all the user databases.
Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. For example:
```
start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=test SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI
```
For more information about rebuilding the master database, see How to: Rebuild the Master Database for SQL Server 2005.
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 2005, you can specify a default collation for each new database you create.

Concepts

Other Resources

Help and Information