Changing SQL Server Collation on a SQL Server 2008 Failover Cluster

  1. Log-in to the active node.
  2. Trigger a full backup for all System and User Databases.
  3. Script out all log-ins.
  4. If there are any user databases, you will have to export the tables using BCP utility.
  5. Copy SQL Server 2008 install media folder to some local directory. For Eg: D:\SQL2008SP2
  6. Open command prompt and execute below command. Caution: This step will replace your current system databases with new ones with required collation. So please make sure you will be able to restore all logins and the user databases back after this activity.
    D:\SQL2008SP2\Setup.exe /QUIET /ACTION=REBUILDDATABASE
    /INSTANCENAME=MSSQLSERVER
    /SQLSYSADMINACCOUNTS=”DOMAIN\ntid” /SAPWD=strongpassword
    /SQLCOLLATION=New_Collation_Name

    Note: Please replace the values for /INSTANCENAME, /SQLSYSADMINACCOUNTS, /SAPWD and /SQLCOLLATION

    Here you go !. Your SQL server has got new collation settings now. Please let me know if incase you experienced any issues.

  7. Now restore all logins and userdatabases using the scripts generated in step 3 and the tables exported in Step 4.

Reference: http://msdn.microsoft.com/en-us/library/ms179254.aspx


Author: Sherbaz

Being a gadget enthusiast, He always felt good to try new programming languages and techniques. But never goes in-depth anywhere. www.SplitExpense.in was found when he tried php, html, javascript and mysql. Being an electronics engineer, he is also interested to build small gadgets and tools in embedded and digital electronics. As a profession, he handles microsoft sql server database and calls himself a database administrator

Leave a Reply