Categories
DBA

How to change Server Collation in SQL Server 2008

In this article we are going to discuss about changing SQL Server 2008 collation at serverlevel. While installing SQL Server 2008 we may miss to choose the right collation and we need to rectify this by changing the collation at serverlevel. You can change the collation of sql server without uninstalling. Lets discuss the necessary steps for changing collation for sql server.

Steps for changing collation

  • Take backup of all the databases & logins exists in the server for safer side.
  • Detach all the user databases
  • Insert SQL Server 2008 CD DVD into drive.
  • Below is the syntax for changing the collation at serverlevel, please note that this will rebuild all the system databases in that instance/.
setup.exe /q /ACTION=RebuildDatabase /INSTANCENAME=InstanceName /SAPWD="New SA Password" /SQLCollation=CollationName /SQLSYSADMINACCOUNTS="Admin ID"

Where,

/q – perform silent installation

/Action – We are rebuilding the system databases to change the collation hence the parameter is always RebuildDatabase only

/INSTANCENAME – Name of the instance you are going to change the collation

/SAPWD – Provide new password for SA login

/SQLCollation – Provide the new collation name of SQL Server

/SQLSYSADMINACCOUNTS – Provide a account name which has admin rights in sql server. Please note that this account should be windows authenticated account having sysadmin privilege in sql server

  • Once its done check the new collation of sql server
  • Attach all the user databases to SQL Server and re-create the logins.
  • Check application functionality.

I’m going to test the above steps with an SQL Server 2008 environment which has an existing collation “SQL_Latin1_General_CP1_CI_AS (Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page 1252 for non-Unicode Data)” to the collation “SQL_Ukrainian_CP1251_CS_AS (Ukrainian, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 107 on Code Page 1251 for non-Unicode Data)”

Sample exercise to change the collation

  • Before changing the collation you can find the collation name.

collation_sql2008_1

  • Execute the below command in Dos prompt to start changing the collation
M:SQL2k8setup.exe /q /ACTION=RebuildDatabase /INSTANCENAME=SQLEXPRESS /SAPWD="SQL2K8" /SQLCollation=SQL_Ukrainian_CP1251_CS_AS /SQLSYSADMINACCOUNTS="SAGARSYSAdmin"

Where M: is my DVD drive letter.

collation_sql2008_2

  • Since this is silent installation it wont ask anything it will just start working on it, once its done the DOS prompt will be like below

collation_sql2008_3

  • You can find from the screenshot below is that the new collation will be Cyrillic_General_CI_AI. That’s all collation change has successfully completed.

collation_sql2008_4

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*