SQL-Articles

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

Posted

in

by

VidhyaSagar

Tags:

, , , , ,

Comments

8 responses to “How to change Server Collation in SQL Server 2008”

  1. Naser avatar
    Naser

    It seems that these instructions achieve nothing, after running setup the server collation remains unchanged. Is there actually any way to do this other than reinstalling?

    Reply
  2. admin avatar
    admin

    @Naser — You might be doing something wrong, I’m not sure what your command is. I have tested it and it’s working. No other go for you, to change server level collation , you need to reinstall or rebuild SQL Server.

    Reply
  3. SQL Server Collations Explained – Dilip's blog on MICROSOFT DYNAMICS AX – AX Technical Blogs – Microsoft Dynamics Community

    […] level or database levelhttp://www.db-staff.com/index.php/microsoft-sql-server/69-change-collationhttp://sql-articles.com/articles/dba/how-to-change-server-collation-in-sql-server-2008/ Windows Collation sorting stylehttp://msdn.microsoft.com/en-us/library/ms143515.aspxCheck […]

    Reply
  4. Carson avatar
    Carson

    What does the INSTANCENAME mean here? I couldn’t proceed with my SSIS setup because of wrong code page. Could you suggest what to write for INSTANCENAME in this case? Thanks.

    Reply
    1. VidhyaSagar avatar
      VidhyaSagar

      @Carson – For default instance it’s MSSQLSERVER for named instance you need to provide the instance name for this parameter

      Reply
  5. Setting and Changing Collation – SQL Server 2008 « Blog

    […] to sql-articles.comRead more: http://sql-articles.com/articles/dba/how-to-change-server-collation-in-sql-server-2008/#ixzz1pu2S8XW… Like this:LikeBe the first to like this […]

    Reply
  6. Zukunftsmusik avatar
    Zukunftsmusik

    Thanks! That helped my a lot. But there’s a little mistake:
    SQL_Latin1_General_CP1_CI_AS stands for “Latin1-General, case-insensitive, accent-sensitive […]” therefore sort order 52.

    If you want to set the sort order to 54 the correct collation would be “SQL_Latin1_General_CP1_CI_AI” 😉

    Reply
  7. Enlaces sobre el cambio de idioma del DW – DTC2Mobility: Desde el DataCenter a la Movilidad

    […] http://sql-articles.com/articles/dba/how-to-change-server-collation-in-sql-server-2008/ […]

    Reply

Leave a Reply

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