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:
<pre class="lang:batch decode:true" title="Instance Collation Change">setup /quiet /action=REBUILDDATABASE /INSTANCENAME=NameOfInstanceWithoutNetworkResourceName /SQLSYSADMINACCOUNTS=Domain\Group /SQLCOLLATION=CollationName
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.