Change Instance Collation on a SQL Server 2008 or higher

Important note before you start:

  • If you perform this whereas there is already data, ALL DATA will be lost as the master, model and msdb databases are recreated during this procedure.
  • In fact, all user databases will have to be re-attached and any link (like login to user) have to be re-created.
  • Since system databases are re-created, jobs and every instance setting has to be re-done

For a cluster,

  • stop the cluster resource holding the SQL Server Instance using the Failover cluster Manager or your favorite command-line tool
  • move the resource on the node when you intend to run the commands… or go to the node holding the resource to run the commands

Given some bugs seen on Connect, better access the SQL distribution files from a DVD or its emulation (mounted ISO, etc.)

Then issue the following command:

Frequent requested CollationName  are: Latin1_General_bin, Latin1_General_CI_AS or SQL_Latin1_General_CP1_CI_AS

You can remove the /quiet to get the progress screens and the buttons to clicks. If your instance is the default one, use MSSQLSERVER as InstanceName.

Please note that this command works for Windows Authentication mode SQL Servers. If you are in mixed mode, you may either temporarily switch to Windows Only or use the SAPWD switch to add the SA password.

Leave a comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.