Changing SQL Server 2008/2008 R2 Collation

The server collation acts as the default collation for all system databases that are installed with the instance of SQL Server including newly created user databases. Changing the default SQL Server collation can be a complex operation so if you can please avoid must especially those with large user databases.

Follow the steps below to change the SQL Server collation for newly installed SQL rather than having to re-install SQL all over again.

Steps:

– Log into SQL Server and ensure that you have the SQL server installation media available either on CD or copied to local disk

– Open the command prompt, and change directory or drive to where the SQL Server media files are

– Enter the following command and press enter:

setup.exe /q /ACTION=RebuildDatabase /INSTANCENAME=”Enter Instance Name” /SAPWD=”Strong Password” /SQLCollation=SQL_Latin1_ General_ CP1_CI_AS /SQLSYSADMINACCOUNTS=”Domain\User Account

Where:

INSTANCENAME= Enter the SQL instance name if not default (MSSQLSERVER)

SAPWD= Enter a new STRONG SA password like HGB874t2NeK1V

SQLCollation= Enter the new collation

SQLSYSADMINACCOUNTS= Enter a domain user account with Admin rights

IMPORTANT

Standalone SQL:

– Follow the steps mentioned above. Note: remember the process removes security groups from SQL and the accounts needs to be re-added back

Clustered SQL instance:

– Log into the Active node

– Pulse the passive node from Cluster Admin

– Take SQL group Offline and then run the above command

– After completion, re-add the security groups

 
comments powered by Disqus