How to Change SQL Instance Collation

How to Change SQL Instance Collation

In this post I am going to show you how to change SQL 2012 database collation SQL_Latin1_General_CP1_CI_AS, the requirement for System Centre Configuration Manager (SCCM) 2012.  Changing database collation means all data in the database will be lost - make sure you know what your doing.

I needed to change the database collation for my SCCM instance in relation to an SCCM installation error:

Configuration Manager requires that you configure your SQL Server instance and Configuration Manager site database (if already present) to use the SQL_Latin1_General_CP1_CI_AS collation, unless you are using a Chinese operating system and require GB18030 support.

To change the database collation simply run the setup.exe from the SQL Installation media again but in quite mode.  The command you need to run is:

setup /q /action=rebuilddatabase /instancename=sccm /sapwd=P@ssw0rd /SQLCollation=SQL_Latin1_General_CP1_CI_AS /SQLSYSADMINACCOUNTS=domain\administrator

/q – perform silent installation

/Action – [RebuildDatabase ] Rebuilding the system databases to change the collation name

/INSTANCENAME – Name of the instance the collation has to change

- If Default Instance then “MSSQLSERVER”

- If Named Instance “Named Instance Name”

/SAPWD – Provide new password for SA login

 - Enable SA Account if it Disabled setup with Strong Password.

/SQLCollation – Provide the new collation name of SQL Server

/SQLSYSADMINACCOUNTS – Provide account name which has admin rights in sql server.