Setting and Changing the Server Collation

Setting and Changing the Server Collation
SQL Server 2005

SQL Server 2008 Books Online (October 2009)

Updated: 30 April 2009

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.

Change History

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

Community Content

Change collation in SQL Server 2008

Added by:shenwood

Also see:

http://blogs.msdn.com/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx

Restoring Databases

Added by:GeoffCollishaw

The documentation above suggests that you need to have a script to recreate your user databases and then you need to import their data.
I found that I could just re-attach the databases and all was fine.

Simply re-attaching user database(s) may not give you the result you expect?

Added by:BSaragozza

The method described in BOL of manually scripting tables and exporting/importing data using BCP is the better method as this will allow you to ensure that the user databases are using the same collation as the server default.

Why is this a concern? Well there are several reasons but a good example is the use of temp tables. Temp tables are stored in a system database named tempdb the collection method used for tempdb is the server default, if this is different to the user database then attempts to join character based data between tables in your database and temp tables will generate collaction errors.

The detach and re-attach method will allow you to bring your user database back online when you have re-built the system databases, however, you will still need to make changes to ensure the database collection is the same as the server collaction if this is your desired end result.

Concepts

Other Resources

Help and Information