How to change Server Collation in SQL Server 2005

In this article we are going to discuss about changing SQL Server 2005 collation at serverlevel. While installing SQL Server 2005 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 2005 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.
start /wait setup.exe /qb INSTANCENAME=InstanceName REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=test SQLCOLLATION=CollationName

Where,

/qb – perform silent installation

Instancename – Name of the instance you are going to change the collation

Reinstall – This should be in SQL_Engine since we are going to change collation of database engine

Rebuilddatabase – This should be in 1 since we are commanding sql server to rebuild the database with new collation

SAPWD – Provide new password for SA login

SQLCollation – Provide the new collation name of 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 2005 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 “Cyrillic_General_CI_AI (Cyrillic-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive)”

Sample exercise to change the collation

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

collation_1

  • Execute the below command in Dos prompt to start changing the collation
start /wait M:Serverssetup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=test SQLCOLLATION=Cyrillic_General_CI_AI

Where M: is my DVD drive letter.

collation_2

  • Once the process started, setup support files will initialize and ask you for confirmation to proceed with the collation change. Please Note that changing the collation will rebuild all the system databases in that instance.

collation_3

  • Once Yes is clicked installer will start the process and will complete the collation change.

collation_4

  • 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_5


Posted

in

by

Comments

Leave a Reply

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