Click to Rate and Give Feedback
MSDN
MSDN Library
SQL Server
SQL Server 2008
Database Engine
Development
 Setting and Changing the Server Col...
Community Content
In this section
Statistics Annotations (3)
Collapse All/Expand All Collapse All
Other versions are also available for the following:
SQL Server 2008 Books Online (October 2009)
Setting and Changing the Server Collation

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 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.
ms179254.note(en-us,SQL.100).gifNote:
Instead of changing the default collation of an instance of SQL Server, you can specify a default collation for each new database you create.

Updated content

Corrected the syntax required to rebuild the master database.

Tags What's this?: Add a tag
Community Content   What is Community Content?
Add new content RSS  Annotations
Change collation in SQL Server 2008      Sarah Henwood ... shenwood   |   Edit   |   Show History
Restoring Databases      GeoffCollishaw   |   Edit   |   Show History
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.
Tags What's this?: Add a tag
Flag as ContentBug
Simply re-attaching user database(s) may not give you the result you expect?      BSaragozza   |   Edit   |   Show History
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.
Flag as ContentBug
Processing
© 2009 Microsoft Corporation. All rights reserved. Terms of Use | Trademarks | Privacy Statement | Site Feedback
Page view tracker